From: Torsten Zühlsdorff <foo@meisterderspiele.de>
To: Dmitriy Igrishin <dmitigr@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Re: [TRIGGER] Returning values from BEFORE UPDATE trigger, without using them
Date: Mon, 26 Jul 2010 09:41:23 +0200
Message-ID: <4C4D3C23.2090804@meisterderspiele.de> (raw)
In-Reply-To: <AANLkTimqui3mjZE3wwSfhQFjn0aeemf0m1XoM0JBEu8-@mail.gmail.com>
References: <i2c3bp$v3l$1@news.eternal-september.org>
<AANLkTimqui3mjZE3wwSfhQFjn0aeemf0m1XoM0JBEu8-@mail.gmail.com>
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
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: foo@meisterderspiele.de, dmitigr@gmail.com
Subject: Re: [TRIGGER] Returning values from BEFORE UPDATE trigger, without using them
In-Reply-To: <4C4D3C23.2090804@meisterderspiele.de>
* 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