Thread: Functions and Triggers

Hello there!

here is what i want to realize:

a trigger, that puts a now() in the last_updated field, on any update of
that table.

i read the manual but i wasnt able to make a working function.
what is the return value here? is there any or is it void?

has somebody a example for me that is similary to my problem?

thank you alot!

Greetings
Norbert


--

Norbert Schollum
-----------------------------------------------
equinux Aktiengesellschaft
Informationstechnologien
Gabelsbergerstr. 30
80333 München - Germany
Tel. 089/520465-0
Fax. 089/520465-299
mailto:schollum@equinux.de
http://www.equinux.de

From: "Norbert Schollum" <schollum@equinux.de>
To: <pgsql-sql@postgresql.org>
Sent: Monday, March 26, 2001 10:24 AM
Subject: [SQL] Functions and Triggers


> Hello there!
>
> here is what i want to realize:
>
> a trigger, that puts a now() in the last_updated field, on any update of
> that table.
>
> i read the manual but i wasnt able to make a working function.
> what is the return value here? is there any or is it void?

"opaque" - a special value for triggers.

> has somebody a example for me that is similary to my problem?

Yep - see the postgres notes from techdocs.postgresql.org - I've got an
example there of exactly this (it's in the "automating" chapter - sorry,
forget the precise URL)

Also have a look at the Cookbook (linked to from same place) which might
well have more examples.

- Richard Huxton

CREATE FUNCTION lastupdated() RETURNS opaque AS '
begin new.last_updated := CURRENT_TIMESTAMP; return new;
end;
' LANGUAGE 'plpgsql';

CREATE TRIGGER trigname BEFORE INSERT OR UPDATE on tblname FOR EACH ROW EXECUTE PROCEDURE lastupdated();

Note: you could use now() instead of CURRENT_TIMESTAMP

Note2: on a BEFORE trigger you must return new or old.  If you return null
the statement will be aborted. (?)

-Cedar


On Mon, 26 Mar 2001, Norbert Schollum wrote:

> Hello there!
> 
> here is what i want to realize:
> 
> a trigger, that puts a now() in the last_updated field, on any update of
> that table.
> 
> i read the manual but i wasnt able to make a working function.
> what is the return value here? is there any or is it void?
> 
> has somebody a example for me that is similary to my problem?
> 
> thank you alot!
> 
> Greetings
> Norbert
> 
> 
> --
> 
> Norbert Schollum
> -----------------------------------------------
> equinux Aktiengesellschaft
> Informationstechnologien
> Gabelsbergerstr. 30
> 80333 München - Germany
> Tel. 089/520465-0
> Fax. 089/520465-299
> mailto:schollum@equinux.de
> http://www.equinux.de
> 
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
> 

Cedar Cox wrote:
>
> CREATE FUNCTION lastupdated() RETURNS opaque AS '
> begin
>   new.last_updated := CURRENT_TIMESTAMP;
>   return new;
> end;
> ' LANGUAGE 'plpgsql';
>
> CREATE TRIGGER trigname BEFORE INSERT OR UPDATE on tblname
>   FOR EACH ROW EXECUTE PROCEDURE lastupdated();
>
> Note: you could use now() instead of CURRENT_TIMESTAMP
>
> Note2: on a BEFORE trigger you must return new or old.  If you return null
> the statement will be aborted. (?)
   If  you  return NULL from a BEFORE trigger the action on that   particular row will be silently suppressed.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #



_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com