Re: Concatenate table name in Pl/Pgsql

[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=#

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

HTH

Ian Barwick