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:22:48 |
Message-ID: | CAB8KJ=j-BZaUcSjxB4cDFjFnqAtVS28kYp_33Yt8v3xuQb4GBA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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.*;
HTH
Ian Barwick
From | Date | Subject | |
---|---|---|---|
Next Message | Adarsh Sharma | 2013-06-26 05:31:56 | Re: Concatenate table name in Pl/Pgsql |
Previous Message | Adarsh Sharma | 2013-06-26 05:18:44 | Re: Concatenate table name in Pl/Pgsql |