Re: Concatenate table name in Pl/Pgsql

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 :(

Thanks

On Wed, Jun 26, 2013 at 11:14 AM, Ian Lawrence Barwick <barwick(at)gmail(dot)com>wrote:

> > Thanks
> >
> >
> > On Wed, Jun 26, 2013 at 10:52 AM, Ian Lawrence Barwick <
> barwick(at)gmail(dot)com>
> > wrote:
> >>
> >> 2013/6/26 Adarsh Sharma <eddy(dot)adarsh(at)gmail(dot)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(dot)adarsh(at)gmail(dot)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
>