pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedFunctions and Triggers
11+ messages / 7 participants
[nested] [flat]
* Functions and Triggers
@ 2001-03-26 09:24 Norbert Schollum <schollum@equinux.de>
2001-03-26 12:04 ` Re: Functions and Triggers Richard Huxton <dev@archonet.com>
2001-03-26 13:44 ` Re: Functions and Triggers Cedar Cox <cedarc@visionforisrael.com>
0 siblings, 2 replies; 11+ messages in thread
From: Norbert Schollum @ 2001-03-26 09:24 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Functions and Triggers
2001-03-26 09:24 Functions and Triggers Norbert Schollum <schollum@equinux.de>
@ 2001-03-26 12:04 ` Richard Huxton <dev@archonet.com>
1 sibling, 0 replies; 11+ messages in thread
From: Richard Huxton @ 2001-03-26 12:04 UTC (permalink / raw)
To: Norbert Schollum <schollum@equinux.de>; pgsql-sql
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Functions and Triggers
2001-03-26 09:24 Functions and Triggers Norbert Schollum <schollum@equinux.de>
@ 2001-03-26 13:44 ` Cedar Cox <cedarc@visionforisrael.com>
2001-03-27 09:59 ` Re: Functions and Triggers Jan Wieck <JanWieck@Yahoo.com>
1 sibling, 1 reply; 11+ messages in thread
From: Cedar Cox @ 2001-03-26 13:44 UTC (permalink / raw)
To: Norbert Schollum <schollum@equinux.de>; +Cc: pgsql-sql
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
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Functions and Triggers
2001-03-26 09:24 Functions and Triggers Norbert Schollum <schollum@equinux.de>
2001-03-26 13:44 ` Re: Functions and Triggers Cedar Cox <cedarc@visionforisrael.com>
@ 2001-03-27 09:59 ` Jan Wieck <JanWieck@Yahoo.com>
0 siblings, 0 replies; 11+ messages in thread
From: Jan Wieck @ 2001-03-27 09:59 UTC (permalink / raw)
To: Cedar Cox <cedarc@visionforisrael.com>; +Cc: Norbert Schollum <schollum@equinux.de>; pgsql-sql
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Functions and Triggers
@ 2001-06-06 20:13 Hunter, Ray <rhunter@enterasys.com>
0 siblings, 0 replies; 11+ messages in thread
From: Hunter, Ray @ 2001-06-06 20:13 UTC (permalink / raw)
To: pgsql-sql
I need help on creating a sql function that will check four rows in a table
based on that check it will update another row on a different table. I want
to use this function in a trigger statement.
Table 1 (Test Type)
id cat sub-cat test status timestamp
Table 2 (Record)
timestamp card comp_date status
What I am trying to do is when someone enters data in on table 2 the trigger
will verifty the record and then look at all records with the same id and
card type, then it will determine the status of the test, or in other words
it will change the value of status in table 1.
Thanks,
RAY HUNTER
Automated Test Group
ENTERASYS NETWORKS
E-mail: rhunter@enterasys.com
www.enterasys.com
^ permalink raw reply [nested|flat] 11+ messages in thread
* Functions and Triggers
@ 2001-06-20 16:45 Hunter, Ray <rhunter@enterasys.com>
0 siblings, 0 replies; 11+ messages in thread
From: Hunter, Ray @ 2001-06-20 16:45 UTC (permalink / raw)
To: pgsql-sql
Is there anyone that can point me into the right direction for creating a
function and trigger that will do something along these lines:
I have a table that has router card types and a status on them:
TEST_RECORD
CARD STATUS
1. er16-04 0
2. er16-04 er16-08 1
3. ssr8000 ssr8600 0
4. er16-04 1
5. er16-04 ssr8600 0
6. er-16-04 1
I need a trigger with a function that will check the card type and status
and from there determine the over all status of the test in the test_table.
Each tuple represents one test on a specific card(s) and the status is pass
or fail.
Here the overall status that would be updated in the test_table status be
fail, because of the ssr8600 on line 5.
I appreciate any suggestions and/or comments.
RAY HUNTER
Automated Test Group
Software Support Engineer
ENTERASYS NETWORKS
Internal: 53888
Phone: 801 887-9888
Fax: 801 972-5789
Cellular: 801 698-0622
E-mail: rhunter@enterasys.com
www.enterasys.com
^ permalink raw reply [nested|flat] 11+ messages in thread
* Functions and Triggers
@ 2001-06-20 18:42 Hunter, Ray <rhunter@enterasys.com>
0 siblings, 0 replies; 11+ messages in thread
From: Hunter, Ray @ 2001-06-20 18:42 UTC (permalink / raw)
To: pgsql-sql
Is there anyone that can point me into the right direction for creating a
function and trigger that will do something along these lines:
I have a table that has router card types and a status on them:
TEST_RECORD
CARD STATUS
1. er16-04 0
2. er16-04 er16-08 1
3. ssr8000 ssr8600 0
4. er16-04 1
5. er16-04 ssr8600 0
6. er-16-04 1
I need a trigger with a function that will check the card type and status
and from there determine the over all status of the test in the test_table.
Each tuple represents one test on a specific card(s) and the status is pass
or fail.
Here the overall status that would be updated in the test_table status be
fail, because of the ssr8600 on line 5.
I appreciate any suggestions and/or comments.
RAY HUNTER
Automated Test Group
Software Support Engineer
ENTERASYS NETWORKS
Internal: 53888
Phone: 801 887-9888
Fax: 801 972-5789
Cellular: 801 698-0622
E-mail: rhunter@enterasys.com
www.enterasys.com
^ permalink raw reply [nested|flat] 11+ messages in thread
* functions and triggers
@ 2002-08-20 20:29 Martin Marques <martin@bugs.unl.edu.ar>
2002-08-20 21:16 ` Re: functions and triggers Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 1 reply; 11+ messages in thread
From: Martin Marques @ 2002-08-20 20:29 UTC (permalink / raw)
To: pgsql-sql
I'm trying to build a trigger that will update a timestamp field in a table with
the current timestamp, and I just can't make it work.
The problemas are two:
1) when I try to create the trigger, it says that the function doesn't exist.
Why is this happening?
2) How does the trigger tell the function the row number identifier?
I'm really stuck with this.
Saludos... :-)
--
Porqué usar una base de datos relacional cualquiera,
si podés usar PostgreSQL?
-----------------------------------------------------------------
Martín Marqués | mmarques@unl.edu.ar
Programador, Administrador, DBA | Centro de Telematica
Universidad Nacional
del Litoral
-----------------------------------------------------------------
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: functions and triggers
2002-08-20 20:29 functions and triggers Martin Marques <martin@bugs.unl.edu.ar>
@ 2002-08-20 21:16 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2002-08-20 21:37 ` Re: functions and triggers Martin Marques <martin@bugs.unl.edu.ar>
0 siblings, 1 reply; 11+ messages in thread
From: Stephan Szabo @ 2002-08-20 21:16 UTC (permalink / raw)
To: Martin Marques <martin@bugs.unl.edu.ar>; +Cc: pgsql-sql
On Tue, 20 Aug 2002, Martin Marques wrote:
> I'm trying to build a trigger that will update a timestamp field in a table with
> the current timestamp, and I just can't make it work.
>
> The problemas are two:
>
> 1) when I try to create the trigger, it says that the function doesn't exist.
> Why is this happening?
You should probably show us what you were trying to do, but I'm going to
guess that the function doesn't have the right signature. On
current versions, Trigger functions should return opaque and take no
arguments (any arguments given on the create trigger line are passed
in a different fashion).
> 2) How does the trigger tell the function the row number identifier?
I'm not sure what you mean by this. Getting at the row being worked on
depends somewhat on what language you're using.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: functions and triggers
2002-08-20 20:29 functions and triggers Martin Marques <martin@bugs.unl.edu.ar>
2002-08-20 21:16 ` Re: functions and triggers Stephan Szabo <sszabo@megazone23.bigpanda.com>
@ 2002-08-20 21:37 ` Martin Marques <martin@bugs.unl.edu.ar>
2002-08-20 21:59 ` Re: functions and triggers Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 1 reply; 11+ messages in thread
From: Martin Marques @ 2002-08-20 21:37 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone23.bigpanda.com>; +Cc: pgsql-sql
Quoting Stephan Szabo <sszabo@megazone23.bigpanda.com>:
>
> > The problemas are two:
> >
> > 1) when I try to create the trigger, it says that the function doesn't
> exist.
> > Why is this happening?
>
> You should probably show us what you were trying to do, but I'm going to
> guess that the function doesn't have the right signature. On
> current versions, Trigger functions should return opaque and take no
> arguments (any arguments given on the create trigger line are passed
> in a different fashion).
I have this function which works OK.
CREATE FUNCTION ahora (integer) RETURNS integer AS '
UPDATE usuarios SET tmodif = now()
WHERE codigo = $1;
SELECT 1 as RESULT;
' LANGUAGE SQL
> > 2) How does the trigger tell the function the row number identifier?
>
> I'm not sure what you mean by this. Getting at the row being worked on
> depends somewhat on what language you're using.
I thought about a simple SQL that does the update.
You mean I just call the function from the trigger and thats all?
--
Porqué usar una base de datos relacional cualquiera,
si podés usar PostgreSQL?
-----------------------------------------------------------------
Martín Marqués | mmarques@unl.edu.ar
Programador, Administrador, DBA | Centro de Telematica
Universidad Nacional
del Litoral
-----------------------------------------------------------------
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: functions and triggers
2002-08-20 20:29 functions and triggers Martin Marques <martin@bugs.unl.edu.ar>
2002-08-20 21:16 ` Re: functions and triggers Stephan Szabo <sszabo@megazone23.bigpanda.com>
2002-08-20 21:37 ` Re: functions and triggers Martin Marques <martin@bugs.unl.edu.ar>
@ 2002-08-20 21:59 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 0 replies; 11+ messages in thread
From: Stephan Szabo @ 2002-08-20 21:59 UTC (permalink / raw)
To: Martin Marques <martin@bugs.unl.edu.ar>; +Cc: pgsql-sql
On Tue, 20 Aug 2002, Martin Marques wrote:
> Quoting Stephan Szabo <sszabo@megazone23.bigpanda.com>:
>
> >
> > > The problemas are two:
> > >
> > > 1) when I try to create the trigger, it says that the function doesn't
> > exist.
> > > Why is this happening?
> >
> > You should probably show us what you were trying to do, but I'm going to
> > guess that the function doesn't have the right signature. On
> > current versions, Trigger functions should return opaque and take no
> > arguments (any arguments given on the create trigger line are passed
> > in a different fashion).
>
> I have this function which works OK.
>
> CREATE FUNCTION ahora (integer) RETURNS integer AS '
> UPDATE usuarios SET tmodif = now()
> WHERE codigo = $1;
> SELECT 1 as RESULT;
> ' LANGUAGE SQL
Trigger functions have no args and return opaque and I don't think you
can use sql language functions, but I'm not sure. Something like:
create function ahora_trigger() returns opaque as '
begin
NEW.tmodif := now();
return NEW;
end;'
language 'plpgsql';
should work as a before update trigger.
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2002-08-20 21:59 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-03-26 09:24 Functions and Triggers Norbert Schollum <schollum@equinux.de>
2001-03-26 12:04 ` Richard Huxton <dev@archonet.com>
2001-03-26 13:44 ` Cedar Cox <cedarc@visionforisrael.com>
2001-03-27 09:59 ` Jan Wieck <JanWieck@Yahoo.com>
2001-06-06 20:13 Functions and Triggers Hunter, Ray <rhunter@enterasys.com>
2001-06-20 16:45 Functions and Triggers Hunter, Ray <rhunter@enterasys.com>
2001-06-20 18:42 Functions and Triggers Hunter, Ray <rhunter@enterasys.com>
2002-08-20 20:29 functions and triggers Martin Marques <martin@bugs.unl.edu.ar>
2002-08-20 21:16 ` Re: functions and triggers Stephan Szabo <sszabo@megazone23.bigpanda.com>
2002-08-20 21:37 ` Re: functions and triggers Martin Marques <martin@bugs.unl.edu.ar>
2002-08-20 21:59 ` Re: functions and triggers Stephan Szabo <sszabo@megazone23.bigpanda.com>
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