From: | Ian Lawrence Barwick <barwick(at)gmail(dot)com> |
---|---|
To: | Adarsh Sharma <eddy(dot)adarsh(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:44:50 |
Message-ID: | CAB8KJ=hcyyqTY-CGDwNDAVTVjXXh+-yTZudhyLN_mDDgHAtvng@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
> 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
From | Date | Subject | |
---|---|---|---|
Next Message | Joshua Burns | 2013-06-26 05:50:46 | Semi-Pseudo Data Types & Procedure Arguments |
Previous Message | Amit Langote | 2013-06-26 05:43:44 | Re: Archiving and recovering pg_stat_tmp |