From: | Adarsh Sharma <eddy(dot)adarsh(at)gmail(dot)com> |
---|---|
To: | Ian Lawrence Barwick <barwick(at)gmail(dot)com> |
Cc: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Concatenate table name in Pl/Pgsql |
Date: | 2013-06-26 05:31:56 |
Message-ID: | CAGx-Qq+jJ2fK_X8QCRzYtf-DTJ2NpH+DALqzOBG6kAREmWb+KQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(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
>
> HTH
>
> Ian Barwick
>
From | Date | Subject | |
---|---|---|---|
Next Message | Amit Langote | 2013-06-26 05:43:44 | Re: Archiving and recovering pg_stat_tmp |
Previous Message | Ian Lawrence Barwick | 2013-06-26 05:22:48 | Re: Concatenate table name in Pl/Pgsql |