Thread: Concatenate table name in Pl/Pgsql
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=#
EXECUTE 'insert into tmp'||abc|| 'values ( NEW.* )';
This works :)2013/6/26 Adarsh Sharma <eddy.adarsh@gmail.com>: > 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 HTH Ian Barwick
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=#
Thankstest=# 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=#
On Wed, Jun 26, 2013 at 10:52 AM, Ian Lawrence Barwick <barwick@gmail.com> wrote:
2013/6/26 Adarsh Sharma <eddy.adarsh@gmail.com>:You'll need to create a string and use EXECUTE, something along the lines of:> 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 ?
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
HTH
Ian Barwick
> Thanks > > > On Wed, Jun 26, 2013 at 10:52 AM, Ian Lawrence Barwick <barwick@gmail.com> > wrote: >> >> 2013/6/26 Adarsh Sharma <eddy.adarsh@gmail.com>: >> > 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@gmail.com>: > 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
Passing each column seems not good because if column additon/deletion happens in future , we also need to change function as well. I think below will work :-
test=# CREATE OR REPLACE FUNCTION tmp_trigger_function()
test-# RETURNS TRIGGER AS $$
test$# DECLARE
test$# abc varchar;
test$# BEGIN
test$# select to_char(NEW.a::timestamp,'yyyymmdd') into abc ;
test$# EXECUTE 'insert into tmp'||abc|| ' values ' || '( NEW.* )';
test$# RETURN NULL;
test$# END;
test$# $$
test-# LANGUAGE plpgsql;
CREATE FUNCTION
Time: 0.913 ms
test=# insert into tmp values ('Tom','2013-06-03');
ERROR: missing FROM-clause entry for table "new"
LINE 1: insert into tmp20130603 values ( NEW.* )
^
QUERY: insert into tmp20130603 values ( NEW.* )
CONTEXT: PL/pgSQL function tmp_trigger_function() line 6 at EXECUTE statement
Time: 0.749 ms
test=#
But don't understand why it is taking NEW as table. Clearly shows table name is concatenated but data insertion fails :(test=# CREATE OR REPLACE FUNCTION tmp_trigger_function()
test-# RETURNS TRIGGER AS $$
test$# DECLARE
test$# abc varchar;
test$# BEGIN
test$# select to_char(NEW.a::timestamp,'yyyymmdd') into abc ;
test$# EXECUTE 'insert into tmp'||abc|| ' values ' || '( NEW.* )';
test$# RETURN NULL;
test$# END;
test$# $$
test-# LANGUAGE plpgsql;
CREATE FUNCTION
Time: 0.913 ms
test=# insert into tmp values ('Tom','2013-06-03');
ERROR: missing FROM-clause entry for table "new"
LINE 1: insert into tmp20130603 values ( NEW.* )
^
QUERY: insert into tmp20130603 values ( NEW.* )
CONTEXT: PL/pgSQL function tmp_trigger_function() line 6 at EXECUTE statement
Time: 0.749 ms
test=#
On Wed, Jun 26, 2013 at 11:14 AM, Ian Lawrence Barwick <barwick@gmail.com> wrote:
2013/6/26 Adarsh Sharma <eddy.adarsh@gmail.com>:> Thanks
>
>
> On Wed, Jun 26, 2013 at 10:52 AM, Ian Lawrence Barwick <barwick@gmail.com>
> wrote:
>>
>> 2013/6/26 Adarsh Sharma <eddy.adarsh@gmail.com>:
>> > 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> Thanks Ian for such a quick response. But my NEW.* will be 10-15 columnsYou'll need to specify each column explicitly if there's more than one.
> 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=#
Regards
Ian Barwick
Able to fix but still new error :(
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 ' || NEW.* ;
test$# EXECUTE stmt;
test$# RETURN NULL;
test$# END;
test$# $$
test-# LANGUAGE plpgsql;
CREATE FUNCTION
Time: 0.996 ms
test=# insert into tmp values ('2013-06-02','Start','process');
ERROR: column "2013-06-02 00:00:00" does not exist
LINE 1: insert into tmp20130602 values ("2013-06-02 00:00:00",Start,...
^
QUERY: insert into tmp20130602 values ("2013-06-02 00:00:00",Start,process)
CONTEXT: PL/pgSQL function tmp_trigger_function() line 8 at EXECUTE statement
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 ' || NEW.* ;
test$# EXECUTE stmt;
test$# RETURN NULL;
test$# END;
test$# $$
test-# LANGUAGE plpgsql;
CREATE FUNCTION
Time: 0.996 ms
test=# insert into tmp values ('2013-06-02','Start','process');
ERROR: column "2013-06-02 00:00:00" does not exist
LINE 1: insert into tmp20130602 values ("2013-06-02 00:00:00",Start,...
^
QUERY: insert into tmp20130602 values ("2013-06-02 00:00:00",Start,process)
CONTEXT: PL/pgSQL function tmp_trigger_function() line 8 at EXECUTE statement
On Wed, Jun 26, 2013 at 11:26 AM, Adarsh Sharma <eddy.adarsh@gmail.com> wrote:
ThanksPassing each column seems not good because if column additon/deletion happens in future , we also need to change function as well. I think below will work :-But don't understand why it is taking NEW as table. Clearly shows table name is concatenated but data insertion fails :(
test=# CREATE OR REPLACE FUNCTION tmp_trigger_function()
test-# RETURNS TRIGGER AS $$
test$# DECLAREtest$# abc varchar;test$# EXECUTE 'insert into tmp'||abc|| ' values ' || '( NEW.* )';
test$# BEGIN
test$# select to_char(NEW.a::timestamp,'yyyymmdd') into abc ;CREATE FUNCTION
test$# RETURN NULL;
test$# END;
test$# $$
test-# LANGUAGE plpgsql;
Time: 0.913 ms
test=# insert into tmp values ('Tom','2013-06-03');
ERROR: missing FROM-clause entry for table "new"
LINE 1: insert into tmp20130603 values ( NEW.* )
^
QUERY: insert into tmp20130603 values ( NEW.* )
CONTEXT: PL/pgSQL function tmp_trigger_function() line 6 at EXECUTE statement
Time: 0.749 ms
test=#On Wed, Jun 26, 2013 at 11:14 AM, Ian Lawrence Barwick <barwick@gmail.com> wrote:2013/6/26 Adarsh Sharma <eddy.adarsh@gmail.com>:> Thanks
>
>
> On Wed, Jun 26, 2013 at 10:52 AM, Ian Lawrence Barwick <barwick@gmail.com>
> wrote:
>>
>> 2013/6/26 Adarsh Sharma <eddy.adarsh@gmail.com>:
>> > 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> Thanks Ian for such a quick response. But my NEW.* will be 10-15 columnsYou'll need to specify each column explicitly if there's more than one.
> 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=#
Regards
Ian Barwick
Final Fix :
execute 'insert into tmp'||abc|| ' select $1.*' using new;
Thanks execute 'insert into tmp'||abc|| ' select $1.*' using new;
On Wed, Jun 26, 2013 at 12:18 PM, Adarsh Sharma <eddy.adarsh@gmail.com> wrote:
Able to fix but still new error :(test$# stmt := 'insert into tmp'||abc|| ' values ' || NEW.* ;
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$# EXECUTE stmt;Time: 0.996 ms
test$# RETURN NULL;
test$# END;
test$# $$
test-# LANGUAGE plpgsql;
CREATE FUNCTION
test=# insert into tmp values ('2013-06-02','Start','process');
ERROR: column "2013-06-02 00:00:00" does not exist
LINE 1: insert into tmp20130602 values ("2013-06-02 00:00:00",Start,...
^
QUERY: insert into tmp20130602 values ("2013-06-02 00:00:00",Start,process)
CONTEXT: PL/pgSQL function tmp_trigger_function() line 8 at EXECUTE statementOn Wed, Jun 26, 2013 at 11:26 AM, Adarsh Sharma <eddy.adarsh@gmail.com> wrote:ThanksPassing each column seems not good because if column additon/deletion happens in future , we also need to change function as well. I think below will work :-But don't understand why it is taking NEW as table. Clearly shows table name is concatenated but data insertion fails :(
test=# CREATE OR REPLACE FUNCTION tmp_trigger_function()
test-# RETURNS TRIGGER AS $$
test$# DECLAREtest$# abc varchar;test$# EXECUTE 'insert into tmp'||abc|| ' values ' || '( NEW.* )';
test$# BEGIN
test$# select to_char(NEW.a::timestamp,'yyyymmdd') into abc ;CREATE FUNCTION
test$# RETURN NULL;
test$# END;
test$# $$
test-# LANGUAGE plpgsql;
Time: 0.913 ms
test=# insert into tmp values ('Tom','2013-06-03');
ERROR: missing FROM-clause entry for table "new"
LINE 1: insert into tmp20130603 values ( NEW.* )
^
QUERY: insert into tmp20130603 values ( NEW.* )
CONTEXT: PL/pgSQL function tmp_trigger_function() line 6 at EXECUTE statement
Time: 0.749 ms
test=#On Wed, Jun 26, 2013 at 11:14 AM, Ian Lawrence Barwick <barwick@gmail.com> wrote:2013/6/26 Adarsh Sharma <eddy.adarsh@gmail.com>:> Thanks
>
>
> On Wed, Jun 26, 2013 at 10:52 AM, Ian Lawrence Barwick <barwick@gmail.com>
> wrote:
>>
>> 2013/6/26 Adarsh Sharma <eddy.adarsh@gmail.com>:
>> > 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> Thanks Ian for such a quick response. But my NEW.* will be 10-15 columnsYou'll need to specify each column explicitly if there's more than one.
> 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=#
Regards
Ian Barwick