Greenplum 4.3 doesn't support this 'USING' grammar

Question

EXECUTE 'INSERT INTO ' || tablename_2 || ' VALUES (($1).*)' USING NEW ;

Greenplum (based on Postgres 8.2) doesn't support this 'using' grammar, how to do this operator in Greenplum 4.3

Error info:

ERROR: syntax error at or near "USING" LINE 1: ...LECT 'INSERT INTO ' || $1 ||' VALUES (($1).)' USING $2 ^ QUERY: SELECT 'INSERT INTO ' || $1 ||' VALUES (($1).)' USING $2 CONTEXT: SQL statement in PL/PgSQL function "dp_insert_trigger" near line 13

Answer 1

The USING clause for dynamic commands was introduced in version 8.4. In 8.2 you have to assemble the string with all of its dynamic parts using string concatenation and the quote_literal(), quote_identifier() and quote_nullable() functions.

PG 8.2 is unsupported since December 2011 and even the respectable 8.4 is beyond its lifetime for more than 18 months now. You should really upgrade.

Answer 2

Dynamic SQL in Greenplum requires the entire SQL statement to be created and it doesn't support "using new".

v_sql := 'insert into ' || p_target_table_name || ' (col1, col2, col3) ' || 
         'select col1, col2, col3 from ' || p_source_table_name;

execute v_sql;

Singleton insert statements should be avoided in Greenplum because it is so very slow. Instead, do bulk operations. This lack of a feature has never come up because inserting into a table row by row is so heavily discouraged that this feature isn't needed.