0
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

user5107182
  • 21
  • 5

2 Answers2

0

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.

Jon Roberts
  • 2,068
  • 1
  • 9
  • 11
-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.

Patrick
  • 29,357
  • 6
  • 62
  • 90
  • thanks but i use greenplum 4.3.5.x.its pg is 8.2,i have no way. and could u please type the complete statement about my sql? i am poor in pg.thank u. – user5107182 Feb 02 '16 at 06:16
  • He isn't using PostgreSQL. Greenplum is a fork of PostgreSQL 8.2. – Jon Roberts Feb 02 '16 at 23:16
  • @JonRoberts: Greenplum was not mentioned in the question when I answered it, PostgreSQl 8.2 was. The reference to GP came only 3 hours later in his comment and then a_horse_with_no_name edited the question. So if you made the down-vote, you may want to reconsider that. – Patrick Feb 03 '16 at 00:58
  • Greenplum (which internally shows PG version 8.2) and PostgreSQL version 8.2 are two different products. Right now Pivotal merges newer PostgreSQL versions, but it will take some time to support this feature. – A. Scherbaum Feb 05 '16 at 17:08