Обсуждение: [TRIGGER] Returning values from BEFORE UPDATE trigger, without using them

Hello,

i have a non-trival problem and i do not believe that it is solvable.

I have defined an BEFORE UPDATE trigger. The trigger catch every update, 
change some columns of the new row, make an insert of the new row and 
returns null to abort the update.
All fine till here :)

Now the problem: if i do an UPDATE table [..] RETURNING * it returns 
always null. This is correct behavior, because the UPDATE is canceld by 
the trigger which returns null, so the updated data is "null".
But i want to get the new data inserted by the trigger without a new 
select. Is there a possibility to get the inserted data or do i have to 
do a select after the update?

Greetings,
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.

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

Torsten Zühlsdorff <foo@meisterderspiele.de> writes:
> I have defined an BEFORE UPDATE trigger. The trigger catch every update, 
> change some columns of the new row, make an insert of the new row and 
> returns null to abort the update.

Why in the world would you do that?  Just return the modified row from
the trigger and let the update proceed normally.
        regards, tom lane

Tom Lane schrieb:
> Torsten Zühlsdorff <foo@meisterderspiele.de> writes:
>> I have defined an BEFORE UPDATE trigger. The trigger catch every update, 
>> change some columns of the new row, make an insert of the new row and 
>> returns null to abort the update.
> 
> Why in the world would you do that?  Just return the modified row from
> the trigger and let the update proceed normally.

Because of content-revision. I have a lot of text which is modified and 
created from multiple persons. The requierment is, that i need every 
version of every content (+ their meta-data).
And i don't need just a log, which lists the changes. The different 
revisions of *one* content are used at the *same* time at *different* 
parts of the website. Therefore i rewrite every UPDATE to an INSERT. And 
except of the missing returning everything works fine and fast. :)

Greetings,
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.