Re: Triggers and SQL functions
От | Philippe Rochat (RSR: 318 17 93) |
---|---|
Тема | Re: Triggers and SQL functions |
Дата | |
Msg-id | 3627C744.E37E933@lbdsun.epfl.ch обсуждение исходный текст |
Ответ на | Triggers and SQL functions (Pierre Padovani <pierre@desertmoon.com>) |
Список | pgsql-sql |
pierre@desertmoon.com wrote: > > > > > I have exactly the same problem as you mentioned in a mail > > sent to postgres mailing lists about creating trigger using > > sql function ... > > > > Did you get the solution to you problem ? > > > > Ph.R. > > > > > > I actually found the solution in the docs. The solution is to understand > the difference between triggers and rules. Triggers are used primarily > to call C/C++ library functions that you can create and add onto the > postgresql system. > > Rules on the other-hand act just like 'triggers' but only call/usr > sql to perform their functions. > > So the solution: Use rules to create trigger-like actions that run > stored sql procedures. There is a man page on 'create rule' that you > can use to get more info. > > -=pierre The problem is the following: CREATE TABLE user ( id int, username char(10), passwd char(10) ); CREATE sequence user_seq start 1 increment 1; CREATE RULE adduser as on insert to user where id isnull do update user set id=nextval('user_seq') where id isnull; -- As you can see, I implement kind of autoinc on id. CREATE TABLE user_realm ( uid int, rlid int, adminuser boolean ); I would like to defaultly add a user in the realm 1. I tried the following: CREATE RULE adduservoid as on insert to user do insert into user_realm values(new.id, 1, false); But new.id is very probably null ! The problem is that rules is a rewriting system and not function calls (and so no way to define after/befor). So I would like to do this auto-insert into user_realm with a trigger after insert. I tried the following : -- Makes all user at least belonging to Void ... just in case CREATE FUNCTION adduservoid () returns boolean as 'insert into user_realm select id as uid,1 as rlid,false as adminuser from user where not exists ( select uid from user_realm where user.id=user_realm.uid ); select true as ignore_this;' language 'sql'; CREATE TRIGGER autoadduservoid AFTER INSERT OR UPDATE ON user for each row EXECUTE PROCEDURE adduservoid(); But this doesnt work for the reason you mentioned. Another solution would be if the create rule would support many statements instead of just one, like that: CREATE RULE adduser as on insert to user where id isnull do 'update user set id=nextval('user_seq') where id isnull; insert into user_realm(new.id,1,false);' But because I have also a check_primary key on user_realm that ensure that uid exists in user ... I would still have a problem ! Ph.R. -- _____________________________________________________________________ Philippe Rochat, EPFL DI-LBD Database Laboratory CH-1015 LAUSANNE Swiss Federal Institute of Technology (EPFL) tel:++41 21 693 52 53 Beep-EPFL: 181 4644 fax:++41 21 693 51 95 Private: Grammont, 9 1007 LAUSANNE tel:++41 21 617 03 05 mailto:Philippe.Rochat@epfl.ch, http://lbdwww.epfl.ch/~prochat
В списке pgsql-sql по дате отправления: