Thread: [Trigger] Help needed with NEW.* and TG_TABLE_NAME

Hello,

i have a problem with a trigger written in pl/pgsql.

It looks like this:

CREATE OR REPLACE FUNCTION versionize()
RETURNS TRIGGER
AS $$
BEGIN
  NEW.revision := addContentRevision (OLD.content_id, OLD.revision);
  /* not working line, just a stub:  EXECUTE 'INSERT INTO ' || TG_TABLE_NAME  || ' SELECT $1 ' USING NEW;  */
  RETURN NULL;

END;
$$ LANGUAGE 'plpgsql' VOLATILE;

The function should be used at different tables and is invoked after 
UPDATEs. Everything what happens is the function call of 
addContentRevision. After this call all data (with the updated revision 
column) should be stored in the table as a new row.

My problem: the aim-table is not static. It's just always the table 
which invoked the trigger. The trigger itself could be called at many 
tables. I've tried some other ways of expressing the INSERT but nothing 
worked:
- 'INSERT INTO ' || TG_TABLE_NAME  || ' SELECT NEW.*'
- INSERT INTO TG_TABLE_NAME SELECT NEW.*
- EXECUTE 'INSERT INTO ' || TG_TABLE_NAME  USING NEW;

Do you have any hints?

Greetings from Germany,
Torsten

<p>Torsten, <br /><br />Usually, the “insert ... (select ...)” has the select portion enclosed in parenthesis. Don't
knowif solution is that simple but did not see it in your examples. It may be worth a try. <br /><br />Stuart <br /><br
/><br/>----- Original message ----- <br />> Hello, <br />> <br />> i have a problem with a trigger written in
pl/pgsql.<br />> <br />> It looks like this: <br />> <br />> CREATE OR REPLACE FUNCTION versionize() <br
/>>RETURNS TRIGGER <br />> AS $$ <br />> BEGIN <br />> <br />>      NEW.revision := addContentRevision
(OLD.content_id,OLD.revision); <br />> <br />>      /* not working line, just a stub: <br />>      EXECUTE
'INSERTINTO ' || TG_TABLE_NAME  || ' SELECT $1 ' USING NEW; <br />>      */ <br />> <br />>      RETURN NULL;
<br/>> <br />> END; <br />> $$ LANGUAGE 'plpgsql' VOLATILE; <br />> <br />> The function should be used
atdifferent tables and is invoked after <br />> UPDATEs. Everything what happens is the function call of <br />>
addContentRevision.After this call all data (with the updated revision <br />> column) should be stored in the table
asa new row. <br />> <br />> My problem: the aim-table is not static. It's just always the table <br />> which
invokedthe trigger. The trigger itself could be called at many <br />> tables. I've tried some other ways of
expressingthe INSERT but nothing <br />> worked: <br />> - 'INSERT INTO ' || TG_TABLE_NAME  || ' SELECT NEW.*'
<br/>> - INSERT INTO TG_TABLE_NAME SELECT NEW.* <br />> - EXECUTE 'INSERT INTO ' || TG_TABLE_NAME  USING NEW; <br
/>><br />> Do you have any hints? <br />> <br />> Greetings from Germany, <br />> Torsten <br />> <br
/>>-- <br />> Sent via pgsql-sql mailing list (<a
href="mailto:pgsql-sql@postgresql.org">pgsql-sql@postgresql.org</a>)<br />> To make changes to your subscription:
<br/>> <a href="http://www.postgresql.org/mailpref/pgsql-sql">http://www.postgresql.org/mailpref/pgsql-sql</a><br
/><br/>