Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id 93DD2632280 for ; Tue, 11 May 2010 19:07:10 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 55125-02 for ; Tue, 11 May 2010 22:07:02 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from elasmtp-dupuy.atl.sa.earthlink.net (elasmtp-dupuy.atl.sa.earthlink.net [209.86.89.62]) by mail.postgresql.org (Postfix) with ESMTP id 9ADE763292B for ; Tue, 11 May 2010 19:07:02 -0300 (ADT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=dk20050327; d=earthlink.net; b=Wm1UHdGWbr74S6evszjASgaSFyzxQXTReLboFbhlBw2UbB43oCPOqT4QBjjA7pFl; h=Received:From:Reply-To:To:Subject:X-Mailer:References:In-Reply-To:Content-Type:X-MSMail-Priority:X-Priority:Date:Message-Id:Mime-Version:X-ELNK-Trace:X-Originating-IP; Received: from [24.136.253.42] (helo=[192.168.0.40]) by elasmtp-dupuy.atl.sa.earthlink.net with esmtpa (Exim 4.67) (envelope-from ) id 1OBxbQ-0007Zq-Dx; Tue, 11 May 2010 18:07:00 -0400 From: Stuart Reply-To: Stuart To: Torsten =?ISO-8859-1?Q?Z=FChlsdorff?= , pgsql-sql@postgresql.org Subject: Re: [Trigger] Help needed with NEW.* and TG_TABLE_NAME X-Mailer: Modest 3.1 References: In-Reply-To: Content-Type: multipart/alternative; boundary="=-z9I19lebaVw7ubrpTflC" X-MSMail-Priority: Normal X-Priority: 3 Date: Tue, 11 May 2010 18:06:58 -0400 Message-Id: <1273615618.1838.5.camel@Nokia-N900-02-8> Mime-Version: 1.0 X-ELNK-Trace: 7deb539a379e73741aa676d7e74259b7b3291a7d08dfec79fce44ecb357eb2156930c24093b5bca0350badd9bab72f9c350badd9bab72f9c350badd9bab72f9c X-Originating-IP: 24.136.253.42 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=1.889 tagged_above=-5 required=5 tests=BAYES_20=-0.001, HTML_MESSAGE=0.001, MISSING_MIMEOLE=1.899, RCVD_IN_DNSWL_NONE=-0.0001, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: * X-Archive-Number: 201005/43 X-Sequence-Number: 34091 --=-z9I19lebaVw7ubrpTflC Content-Type: text/plain; charset=utf-8 Content-ID: <1273615617.1838.2.camel@Nokia-N900-02-8> Content-Transfer-Encoding: quoted-printable Torsten, Usually, the =E2=80=9Cinsert ... (select ...)=E2=80=9D has the select porti= on 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 > >=C2=A0 =C2=A0 =C2=A0 NEW.revision :=3D addContentRevision (OLD.content_id,= OLD.revision); > >=C2=A0 =C2=A0 =C2=A0 /* not working line, just a stub: >=C2=A0 =C2=A0 =C2=A0 EXECUTE 'INSERT INTO ' || TG_TABLE_NAME=C2=A0 || ' SE= LECT $1 ' USING NEW; >=C2=A0 =C2=A0 =C2=A0 */ > >=C2=A0 =C2=A0 =C2=A0 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=C2=A0 || ' SELECT NEW.*' > - INSERT INTO TG_TABLE_NAME SELECT NEW.* > - EXECUTE 'INSERT INTO ' || TG_TABLE_NAME=C2=A0 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 --=-z9I19lebaVw7ubrpTflC Content-Type: text/html; charset=utf-8 Content-ID: <1273615617.1838.4.camel@Nokia-N900-02-8> Content-Transfer-Encoding: quoted-printable

Torsten,

Usually, the =E2=80=9Cinsert ... (select ...)=E2=80=9D has the select p= ortion 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 :=3D addContentRevision (OLD.cont= ent_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 aft= er
> UPDATEs. Everything what happens is the function call of
> addContentRevision. After this call all data (with the updated rev= ision
> column) should be stored in the table as a new row.
>
> My problem: the aim-table is not static. It's just always the tabl= e
> which invoked the trigger. The trigger itself could be called at m= any
> tables. I've tried some other ways of expressing the INSERT but no= thing
> 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://ww= w.postgresql.org/mailpref/pgsql-sql

--=-z9I19lebaVw7ubrpTflC--