> Thanks > > > On Wed, Jun 26, 2013 at 10:52 AM, Ian Lawrence Barwick <barwick@xxxxxxxxx> > wrote: >> >> 2013/6/26 Adarsh Sharma <eddy.adarsh@xxxxxxxxx>: >> > Hi , >> > >> > Today i m creating a function that includes dynamic concatenation of a >> > partitioned table name as below :- >> > >> > test=# CREATE OR REPLACE FUNCTION tmp_trigger_function() >> > test-# RETURNS TRIGGER AS $$ >> > test$# DECLARE >> > test$# tbl_name text; >> > test$# abc varchar; >> > test$# BEGIN >> > test$# tbl_name := 'tmp'; >> > test$# select to_char(NEW.a::timestamp,'yyyymmdd') into abc ; >> > test$# insert into tmp || abc values ( NEW.* ); >> > test$# RETURN NULL; >> > test$# END; >> > test$# $$ >> > test-# LANGUAGE plpgsql; >> > ERROR: syntax error at or near "||" >> > LINE 9: insert into tmp || abc values ( NEW.* ); >> > ^ >> > Time: 0.901 ms >> > test=# >> > test=# >> > >> > I tried with a statement variable also. Any ideas ? >> >> You'll need to create a string and use EXECUTE, something along the lines >> of: >> >> stmt := 'insert into ' || tmp || abc || ' VALUES ($1)' >> EXECUTE stmt USING NEW.*; >> >> >> http://www.postgresql.org/docs/current/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN 2013/6/26 Adarsh Sharma <eddy.adarsh@xxxxxxxxx>: > Thanks Ian for such a quick response. But my NEW.* will be 10-15 columns > row. > > > test=# CREATE OR REPLACE FUNCTION tmp_trigger_function() > test-# RETURNS TRIGGER AS $$ > test$# DECLARE > test$# stmt text; > > test$# abc varchar; > test$# BEGIN > test$# select to_char(NEW.a::timestamp,'yyyymmdd') into abc ; > test$# stmt := 'insert into ' || tmp || abc || ' VALUES ($1)' > test$# EXECUTE stmt USING NEW.*; > > test$# RETURN NULL; > test$# END; > test$# $$ > test-# LANGUAGE plpgsql; > ERROR: syntax error at or near "EXECUTE" > LINE 9: EXECUTE stmt USING NEW.*; > ^ > Time: 0.737 ms > test=# You'll need to specify each column explicitly if there's more than one. Regards Ian Barwick -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
