[Trigger] Help needed with NEW.* and TG_TABLE_NAME

From: Torsten Zühlsdorff <foo(at)meisterderspiele(dot)de>
To: pgsql-sql(at)postgresql(dot)org
Subject: [Trigger] Help needed with NEW.* and TG_TABLE_NAME
Date: 2010-05-10 09:35:14
Message-ID: hs8k0i$hdh$1@news.eternal-september.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Gnanakumar 2010-05-10 14:19:04 Invalid message format Exception
Previous Message Justin Graf 2010-05-07 18:43:50 Re: Celko take on EAV