To: pgsql-sql@postgresql.org
Path: not-for-mail
From: Jasen Betts <jasen@xnet.co.nz>
Newsgroups: gmane.comp.db.postgresql.sql
Subject: Re: [Trigger] Help needed with NEW.* and TG_TABLE_NAME
Date: 13 May 2010 10:51:22 GMT
Organization: Dis (not Dat) Organisation
Lines: 67
Message-ID: <hsglja$ofu$1@reversiblemaps.ath.cx>
References: <hsbiuo$rbe$1@news.eternal-september.org>
NNTP-Posting-Host: localhost
Mime-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
User-Agent: slrn/pre1.0.0-2 (Linux)

On 2010-05-11, Torsten Zühlsdorff <foo@meisterderspiele.de> wrote:
> 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 before
> 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.

What many people have missed is that you want to INSERT when the DML
comnabd UPDATE is used.

for things like that usually a rule is used instead, but I can see where
that may be unsuitable for your needs.  I found the following 
to work on a simple test case.


The problem is that INSERT in PLPGSQL needs a fixed table-name, and
that "EXECUTE" can't use variable-names, and further that quote_literal
doesn't convert ROW variables into something that can be used in a
VALUES clause.

so, Here's what I did.

 CREATE OR REPLACE FUNCTION versionize()
 RETURNS TRIGGER
 AS $$
 BEGIN

    -- Not havign a definition for addContentRevision
    -- I had this line commented out during testing.
    NEW.revision := addContentRevision (OLD.content_id, OLD.revision);
    
    EXECUTE 'INSERT INTO '||TG_TABLE_NAME||' SELECT (' || 
       QUOTE_LITERAL(NEW) || '::' || TG_TABLE_NAME ||').*' ;

    RETURN NULL;

 END;
 $$ LANGUAGE PLPGSQL VOLATILE;

I take NEW, convert it to a quoted literal so I can use it in EXECUTE, cast it 
to the apreopreiate row type and split it into columns using SELECT
and .*. That gets inserted.

you should probably use QUOTE_IDENT on the TG_TABLE_NAME and possibly
also use similarly quoted TG_SCHEMA_NAME