passing argument ro a trigger - Mailing list pgsql-admin

From darthxiong@libero.it
Subject passing argument ro a trigger
Date
Msg-id 02022210390208.07798@ivan
Whole thread Raw
List 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.

pgsql-admin by date:

Previous
From: Antony Stace
Date:
Subject: What does PGDG stand for in postgresql-7.2-1PGDG.rpm
Next
From: "Gtrica"
Date:
Subject: Client Authentication