By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,967 Members | 1,690 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,967 IT Pros & Developers. It's quick & easy.

pl/pgsql triggers - Problem with NEW.*

P: 20
Hello there

I have been trying to generate a dynamic querystring thats to be executed inside a trigger function and every time I use NEW.* pl/pgsql throws an error saying 'column * does not exist'.

Expand|Select|Wrap|Line Numbers
  1. CREATE OR REPLACE FUNCTION matrix_test_insert_trigger()
  2.   RETURNS trigger AS
  3. $$
  4.  
  5.  BEGIN
  6.  
  7.     EXECUTE 'INSERT INTO matrix_' || NEW.month || '(id,accept,day,month,year) values ' || NEW.*;
  8.  
  9.  RETURN NULL;
  10.  END;
  11. $$
  12.   LANGUAGE 'plpgsql'
Since this did not work I tried to alter it a bit by explicitly specifying the column names like

Expand|Select|Wrap|Line Numbers
  1. CREATE OR REPLACE FUNCTION matrix_test_insert_trigger()
  2.   RETURNS trigger AS
  3. $$
  4.  
  5.  BEGIN
  6.  
  7.     EXECUTE 'INSERT INTO matrix_' || NEW.month || '(id,accept,day,month,year) values ' || (NEW.id,NEW.accept,NEW.day,NEW.month,NEW.year);
  8.  
  9.  RETURN NULL;
  10.  END;
  11. $$
  12.   LANGUAGE 'plpgsql'

It works fine for non-character columns, I tried to insert values like "INSERT into matrix(id,accept,day,month,year) values (4,'YES',3,3,2007)", but pl/pgsql throws an error for 'YES'. This is because it ignores the quotes when I specify the column names explicitly.

Could anyone please help me out with this? And if you are wondering why the table names are different, they are partitioned tables so there is a child-parent relationship between them.

Any help on this issue would be appreciated!

Cheers
Mar 4 '08 #1
Share this Question
Share on Google+
4 Replies


Expert 100+
P: 700
Try like this
Expand|Select|Wrap|Line Numbers
  1.  CREATE OR REPLACE FUNCTION matrix_test_insert_trigger()
  2.  RETURNS trigger AS$$
  3.  BEGIN
  4.      EXECUTE 'INSERT INTO matrix_' || NEW.month || (id,accept,day,month,year) values( ' || NEW.id ||','|| NEW.accept ||','|| NEW.day ||','|| NEW.month ||','|| NEW.year||')';
  5.  
  6.       RETURN NULL;
  7.  END;
  8.  $$ LANGUAGE 'plpgsql'
  9.  
Does it work for you?
Mar 5 '08 #2

P: 20
Thanks for your resply rski. I was having a play with it and figured out the following works,

Expand|Select|Wrap|Line Numbers
  1. CREATE OR REPLACE FUNCTION matrix_test_insert_trigger()
  2. RETURNS trigger AS $$ 
  3. BEGIN
  4.    EXECUTE 'INSERT INTO matrix_' || NEW.month || '(id,accept,day,month,year) values(' || NEW.id ||,'''|| NEW.accept ||''',|| NEW.day ||,|| NEW.month ||,|| NEW.year||')';
  5.    RETURN NULL;
  6. END;  
  7. $$ LANGUAGE 'plpgsql'
although I would like to know why NEW.* wont work. Its purely because I have around 65 columns and I dont want to go through each of them by hand.

I know something like the following is possible,

Expand|Select|Wrap|Line Numbers
  1.  IF (NEW.month = 03 and NEW.year=2007) THEN
  2.     INSERT INTO landings_2007_03 values (NEW.*);
I use this for a different table, I would like to know if there is a way to achieve something like above when building a dynamic query with NEW.*

Cheers
Mar 6 '08 #3

Expert 100+
P: 700
try function like this
Expand|Select|Wrap|Line Numbers
  1. create or replace function instead_ins() returns trigger as $$
  2. declare
  3. r record;
  4. t text;
  5. x text;
  6. y text;
  7. begin
  8.         select NEW.* into r;
  9.         select r into x;
  10.         y=substring(x from 2 for length(x)-2);
  11.         t='table_prefix_'||NEW.column_name;
  12.         execute 'insert into '||t||' values( '||y||')';
  13.         return NULL;
  14.  
  15. end;
  16. $$ language 'plpgsql';
  17.  
is it helpful?
Mar 9 '08 #4

P: 20
Thanks again for your reply rski.

The function you had posted is a cool work around although I get the following error ,

Syntax error at or near ","
insert into matrix_10 values(2,,,,,,,2007,19,10,,,,,,,,,,,name,,,,,,,,,, ,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,)

I guess its because record type is looking for data for all columns..?? Or is it me overlooking something?

Cheers
Mar 11 '08 #5

Post your reply

Sign in to post your reply or Sign up for a free account.