passing argument ro a trigger

From: darthxiong(at)libero(dot)it
To: pgsql-admin(at)postgresql(dot)org
Subject: passing argument ro a trigger
Date: 2002-02-22 09:39:02
Message-ID: 02022210390208.07798@ivan
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

hi all,
i have two tables, the first contains major numbers and their status, the
second contains major numbers, all their minors, and a action taken over the
major/minor

table 1
major status
1 1
2 1
3 1
4 0
5 2
......

table 2
major minor action
1 0 2
1 1 0
1 2 1
2 0 4
2 1 0
2 2 2
2 3 2
2 4 1
...

so in the table 1, i have to do so:

CREATE FUNCTION align_status( int4 ) RETURNS int2 AS
'
UPDATE table1 SET status = (
SELECT action FROM table2
WHERE major = $1
AND monir = (
SELECT MAX( monir ) FROM table1 WHERE major = $1 )
)
WHERE major = $1;

SELECT status FROM table1 AS RESULT WHERE major = $1 ;
'
LANGUAGE 'SQL';
;

this works fine, i have only to pass to this function the value of the major
to be aligned. I'd like to connect this to a trigger that fires after an
insert in the table2... eg, i insert 2,5,7 in table2 and status in table1
status must be updated to 7.
The function require an argument, how can i pass the major number to it
trought the trigger?

CREATE TRIGGER trigger_align_status
AFTER INSERT ON table2
FOR EACH STATEMENT
EXECUTE PROCEDURE align_status ( UUUAAAAAAAARRGGHHH );

#!/usr/bin/perl
s/UUAARRGGHH/correct method/ if $you_can ;-)

TIA
Ivan

--
Ninety-Ninety Rule of Project Schedules:
The first ninety percent of the task takes ninety percent of
the time, and the last ten percent takes the other ninety percent.

Browse pgsql-admin by date

  From Date Subject
Next Message Gtrica 2002-02-22 10:19:43 Client Authentication
Previous Message Antony Stace 2002-02-22 04:16:17 What does PGDG stand for in postgresql-7.2-1PGDG.rpm