Note: XCONLY: The following description applies only to Postgres-XC.
EXECUTE DIRECT is a SQL query specially created for Postgres-XC to allow to launch queries directly to dedicated nodes determined by a list of nodes nodelist.
Since Postgres-XC 0.9.3, EXECUTE DIRECT is limited to used on 1 node only. Besides, the query sent to remote nodes designed by a list of nodename is limited to SELECT queries. The usage of transaction queries (BEGIN, COMMIT...), DDL, and DML (INSERT, UPDATE, DELETE ) is forbidden to avoid data inconsistency among nodes in the cluster. EXECUTE DIRECT usage is also limited to superusers.
Either Coordinator or Datanode can be selected by its node name.
EXECUTE DIRECT allows the usage of the following utility commands on remote nodes: CREATE TABLESPACE, DROP TABLESPACE.
This mandatory clause specifies the node name on where to launch query. When specifying multiple nodes, node names have to be separated by a comma.
This mandatory clause specifies the raw query to launch on specified node list nodelist. This must be specified as a string literal.
Select some data in a given table tenk1 on remote Datanode named dn1:
EXECUTE DIRECT ON (dn1) 'SELECT * FROM tenk1 WHERE col_char = ''foo''';
Select local timestamp of a remote node named coord2:
EXECUTE DIRECT ON (coord2) 'select clock_timestamp()';
Select list of tables of a remote node named dn50:
EXECUTE DIRECT ON (dn50) 'select tablename from pg_tables';
EXECUTE DIRECT does not conform to the SQL standards, it is a Postgres-XC specific command.