Hello, i'm a portuguese university student doing my final project with a
postgresql database, apache and php3. I have to create an web interface
for doing operations on the pg database. Creates, inserts, drops, removes
etc.
Its nearly finished (i hope), but one of my problems is that i cant find
an example of creation of triggers or functions with sql. I need this to
do a (i hope) simple thing: create a trigger that implements external
keys.
All the trigger has to do is, before inserting a tuple on a table T1, if
that table has an external key on field F1 (from field F2 on table T2),
see if the value of the field F1 on the tuple exists on table T2 (in the
field F2). If it does, continue with the insert, otherwise, dont insert.
I think i have the trigger part ok (although i cant test it without the
function):
create trigger trigg_ext_key_T1 before insert on T1 for each row
execute procedure func_ext_key_T1
I'm very unsure about the function part (still couldnt make it work):
CREATE FUNCTION func_ext_key_T1
(value_of_F1_in_tuple) RETURNS bool /* can i put the value as input? */
AS ' select * from T2 where F2 = value_of_F1_in_tuple
/* i probably cant do the following in pgsql, but i think it has to b something similar */
EXCEPTION WHEN NO_DATA_FOUND THEN begin return false end; '
LANGUAGE 'sql';
But how does the the trigger works? If the function returns (for example)
true he wont insert on the table? Is this how it should work? How is a
value passed from the insert that triggers the trigger to the function?
I'd be very grateful if someone could help me, even if its only showing me
an example of a function with sql that is used on a trigger. It may seem
i'm being lazy, asking instead of searching, but I've been to the postgres
site but couldnt find any examples, and there isnt anyone i know that has
worked with postgres before.
Bye.
JPAjpapa@students.fct.unl.pt
'I love life. And i wanna live. That's what it's all about.'
