Concatenate table name in Pl/Pgsql

2013/6/26 Adarsh Sharma <eddy.adarsh@gmail.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

Thanks

On Wed, Jun 26, 2013 at 10:52 AM, Ian Lawrence Barwick <

barwick@gmail.com>

wrote:

2013/6/26 Adarsh Sharma <eddy.adarsh@gmail.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.adarsh@gmail.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

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@gmail.com>wrote:

Thanks

On Wed, Jun 26, 2013 at 10:52 AM, Ian Lawrence Barwick <

barwick@gmail.com>

wrote:

2013/6/26 Adarsh Sharma <eddy.adarsh@gmail.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.adarsh@gmail.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

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.adarsh@gmail.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@gmail.com

wrote:

Thanks

On Wed, Jun 26, 2013 at 10:52 AM, Ian Lawrence Barwick <

barwick@gmail.com>

wrote:

2013/6/26 Adarsh Sharma <eddy.adarsh@gmail.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.adarsh@gmail.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