Re: Concatenate table name in Pl/Pgsql

Final Fix :

execute 'insert into tmp'||abc|| ' select $1.*' using new;

Thanks

On Wed, Jun 26, 2013 at 12:18 PM, Adarsh Sharma <eddy(dot)adarsh(at)gmail(dot)com>wrote:

> 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
>
>
> On Wed, Jun 26, 2013 at 11:26 AM, Adarsh Sharma <eddy(dot)adarsh(at)gmail(dot)com>wrote:
>
>> 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
>>>
>>
>>
>