From: Stuart <sfbarbee@earthlink.net>
To: Torsten Zühlsdorff <foo@meisterderspiele.de>
To: pgsql-sql@postgresql.org
Subject: Re: [Trigger] Help needed with NEW.* and TG_TABLE_NAME
Date: Tue, 11 May 2010 18:06:58 -0400
Message-ID: <1273615618.1838.5.camel@Nokia-N900-02-8> (raw)
In-Reply-To: <hs8k0i$hdh$1@news.eternal-september.org>
References: <hs8k0i$hdh$1@news.eternal-september.org>
Torsten,
Usually, the “insert ... (select ...)” has the select portion enclosed in parenthesis. Don't know if solution is that simple but did not see it in your examples. It may be worth a try.
Stuart
----- Original message -----
> 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
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: sfbarbee@earthlink.net, foo@meisterderspiele.de
Subject: Re: [Trigger] Help needed with NEW.* and TG_TABLE_NAME
In-Reply-To: <1273615618.1838.5.camel@Nokia-N900-02-8>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox