pl/pgsql triggers - Problem with NEW.*

[pravinasp]

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'.

CREATE OR REPLACE FUNCTION matrix_test_insert_trigger()
  RETURNS trigger AS
$$
 
 BEGIN
 
    EXECUTE 'INSERT INTO matrix_' || NEW.month || '(id,accept,day,month,year) values ' || NEW.*;
 
 RETURN NULL;
 END;
$$
  LANGUAGE 'plpgsql'

Since this did not work I tried to alter it a bit by explicitly specifying the column names like

CREATE OR REPLACE FUNCTION matrix_test_insert_trigger()
  RETURNS trigger AS
$$
 
 BEGIN
 
    EXECUTE 'INSERT INTO matrix_' || NEW.month || '(id,accept,day,month,year) values ' || (NEW.id,NEW.accept,NEW.day,NEW.month,NEW.year);
 
 RETURN NULL;
 END;
$$
  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

[rski]

Try like this

 CREATE OR REPLACE FUNCTION matrix_test_insert_trigger()
 RETURNS trigger AS$$
 BEGIN
     EXECUTE 'INSERT INTO matrix_' || NEW.month || (id,accept,day,month,year) values( ' || NEW.id ||','|| NEW.accept ||','|| NEW.day ||','|| NEW.month ||','|| NEW.year||')';
 
      RETURN NULL;
 END;
 $$ LANGUAGE 'plpgsql'
 

Does it work for you?

[pravinasp]

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

CREATE OR REPLACE FUNCTION matrix_test_insert_trigger()
RETURNS trigger AS $$ 
BEGIN
   EXECUTE 'INSERT INTO matrix_' || NEW.month || '(id,accept,day,month,year) values(' || NEW.id ||,'''|| NEW.accept ||''',|| NEW.day ||,|| NEW.month ||,|| NEW.year||')';
   RETURN NULL;
END;  
$$ 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,

 IF (NEW.month = 03 and NEW.year=2007) THEN
    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

[rski]

try function like this

create or replace function instead_ins() returns trigger as $$
declare
r record;
t text;
x text;
y text;
begin
        select NEW.* into r;
        select r into x;
        y=substring(x from 2 for length(x)-2);
        t='table_prefix_'||NEW.column_name;
        execute 'insert into '||t||' values( '||y||')';
        return NULL;
 
end;
$$ language 'plpgsql';
 

is it helpful?

[pravinasp]

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