subject:"\[GENERAL\] Concatenate table name in Pl\/Pgsql"

Re: [GENERAL] Concatenate table name in Pl/Pgsql

2013-06-26 Thread Adarsh Sharma

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,'mmdd') 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.ada...@gmail.comwrote:

 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,'mmdd') 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 
 barw...@gmail.comwrote:

  Thanks
 
 
  On Wed, Jun 26, 2013 at 10:52 AM, Ian Lawrence Barwick 
 barw...@gmail.com
  wrote:
 
  2013/6/26 Adarsh Sharma eddy.ada...@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,'mmdd') 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.ada...@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,'mmdd') 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

Re: [GENERAL] Concatenate table name in Pl/Pgsql

2013-06-26 Thread Adarsh Sharma

Final Fix :

 execute 'insert into tmp'||abc||  ' select  $1.*' using new;

Thanks


On Wed, Jun 26, 2013 at 12:18 PM, Adarsh Sharma eddy.ada...@gmail.comwrote:

 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,'mmdd') 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.ada...@gmail.comwrote:

 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,'mmdd') 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 barw...@gmail.com
  wrote:

  Thanks
 
 
  On Wed, Jun 26, 2013 at 10:52 AM, Ian Lawrence Barwick 
 barw...@gmail.com
  wrote:
 
  2013/6/26 Adarsh Sharma eddy.ada...@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,'mmdd') 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.ada...@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,'mmdd') 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

[GENERAL] Concatenate table name in Pl/Pgsql

2013-06-25 Thread Adarsh Sharma

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,'mmdd') 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 ?


Thanks

Re: [GENERAL] Concatenate table name in Pl/Pgsql

2013-06-25 Thread Adarsh Sharma

EXECUTE 'insert into tmp'||abc||  'values ( NEW.* )';

This works :)

Re: [GENERAL] Concatenate table name in Pl/Pgsql

2013-06-25 Thread Ian Lawrence Barwick

2013/6/26 Adarsh Sharma eddy.ada...@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,'mmdd') 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


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Concatenate table name in Pl/Pgsql

2013-06-25 Thread Adarsh Sharma

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,'mmdd') 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 barw...@gmail.comwrote:

 2013/6/26 Adarsh Sharma eddy.ada...@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,'mmdd') 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

Re: [GENERAL] Concatenate table name in Pl/Pgsql

2013-06-25 Thread Ian Lawrence Barwick

 Thanks


 On Wed, Jun 26, 2013 at 10:52 AM, Ian Lawrence Barwick barw...@gmail.com
 wrote:

 2013/6/26 Adarsh Sharma eddy.ada...@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,'mmdd') 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.ada...@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,'mmdd') 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


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Concatenate table name in Pl/Pgsql

2013-06-25 Thread Adarsh Sharma

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,'mmdd') 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 barw...@gmail.comwrote:

  Thanks
 
 
  On Wed, Jun 26, 2013 at 10:52 AM, Ian Lawrence Barwick 
 barw...@gmail.com
  wrote:
 
  2013/6/26 Adarsh Sharma eddy.ada...@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,'mmdd') 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.ada...@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,'mmdd') 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

Re: [GENERAL] Concatenate table name in Pl/Pgsql

Re: [GENERAL] Concatenate table name in Pl/Pgsql

[GENERAL] Concatenate table name in Pl/Pgsql

Re: [GENERAL] Concatenate table name in Pl/Pgsql

Re: [GENERAL] Concatenate table name in Pl/Pgsql

Re: [GENERAL] Concatenate table name in Pl/Pgsql

Re: [GENERAL] Concatenate table name in Pl/Pgsql

Re: [GENERAL] Concatenate table name in Pl/Pgsql

8 matches

Site Navigation

Mail list logo

Footer information