Message-ID: <4C4D3C23.2090804@meisterderspiele.de>
Date: Mon, 26 Jul 2010 09:41:23 +0200
From: =?ISO-8859-1?Q?Torsten_Z=FChlsdorff?= <foo@meisterderspiele.de>
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
MIME-Version: 1.0
To: Dmitriy Igrishin <dmitigr@gmail.com>, pgsql-sql@postgresql.org
Subject: Re: [TRIGGER] Returning values from BEFORE UPDATE trigger,
	without using them
References: <i2c3bp$v3l$1@news.eternal-september.org>
	<AANLkTimqui3mjZE3wwSfhQFjn0aeemf0m1XoM0JBEu8-@mail.gmail.com>
In-Reply-To: <AANLkTimqui3mjZE3wwSfhQFjn0aeemf0m1XoM0JBEu8-@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Hey Dmitriy,

thanks for your reply.

> I think, its would be better to use rule on update instead of the trigger
> in such case as you.

I've played the whole weekend with the rule-system, but it didn't work 
for my case. I have a dynamic trigger, which takes cares about revision 
of rows for every table, it is called from. It looks like that:

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

   /* add new version in central register and insert new row */
   NEW.revision := addContentRevision (OLD.content_id, OLD.revision, 
sessval('user_id')::int));

   EXECUTE 'INSERT INTO ' || quote_ident(TG_TABLE_NAME) ||
           ' SELECT (' || QUOTE_LITERAL(NEW) || '::' || 
quote_ident(TG_TABLE_NAME) ||').*' ;

   RETURN NULL;

END;
$$ LANGUAGE 'plpgsql' VOLATILE;

Even if i drop the dynamic INSERT-Part and write it for every relation, 
i wasn't able to figured out how to manipulate the NEW-Record.

The best i tried so far was:
CREATE RULE "versionize"
AS ON UPDATE
TO templates
DO INSTEAD
(
   SELECT addContentRevision (OLD.content_id, OLD.revision, 
sessval('user_id')::int) INTO NEW.revision;
   INSERT INTO templates SELECT NEW.* RETURNING *;
);

But an Updates ends with the ERROR:
"ERROR:  schema "*NEW*" does not exist"

Has anyone a hint how to manipulate the NEW record within an RULE?

Thanks,
Torsten