From: =?UTF-8?B?VG9yc3RlbiBaw7xobHNkb3JmZg==?= <foo@meisterderspiele.de>
Subject: Re: [Trigger] Help needed with NEW.* and TG_TABLE_NAME
Date: Mon, 17 May 2010 10:57:08 +0200
Organization: A noiseless patient Spider
Lines: 83
Message-ID: <hsr0d5$g8m$1@news.eternal-september.org>
References: <hsbiuo$rbe$1@news.eternal-september.org>
	<hsglja$ofu$1@reversiblemaps.ath.cx>
Mime-Version: 1.0
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Injection-Date: Mon, 17 May 2010 08:57:09 +0000 (UTC)
Injection-Info: mx01.eternal-september.org;
	posting-host="AnkPUomeHXvuN7UdHvIkog";
	logging-data="16662"; mail-complaints-to="abuse@eternal-september.org";
	posting-account="U2FsdGVkX19lAbDwYdCZBJnHlOqTQ7Ut"
User-Agent: Mozilla/5.0 (X11; U; Linux i686; de;
	rv:1.8.1.24) Gecko/20100228 Thunderbird/2.0.0.24 Mnenhy/0.7.5.0
In-Reply-To: <hsglja$ofu$1@reversiblemaps.ath.cx>
Cancel-Lock: sha1:Vji+2HEmbv8PeSbWTfGDbNoQltI=
To: pgsql-sql@postgresql.org

Jasen Betts schrieb:

> 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 

That's an quite interesting solution. I've tested it in several ways and 
it works like i want. :)

Thank you very much - and every other responder - for your time.

Greetings from Germany,
Torsten


-- 
http://www.dddbl.de - ein Datenbank-Layer, der die Arbeit mit 8 
verschiedenen Datenbanksystemen abstrahiert,
Queries von Applikationen trennt und automatisch die Query-Ergebnisse 
auswerten kann.