Received: from lbdsun.epfl.ch (root@lbdsun.epfl.ch [128.178.77.9]) by hub.org (8.8.8/8.8.8) with ESMTP id SAA24407; Fri, 16 Oct 1998 18:14:26 -0400 (EDT) (envelope-from prochat@lbdsun.epfl.ch) Received: from lbdsun.epfl.ch (tucana.srg-ssr.ch [146.159.4.9]) by lbdsun.epfl.ch (8.8.X/EPFL-8.1a) with ESMTP id AAA22211; Sat, 17 Oct 1998 00:14:16 +0200 (MET DST) Sender: root@lbdsun.epfl.ch Message-ID: <3627C744.E37E933@lbdsun.epfl.ch> Date: Sat, 17 Oct 1998 00:23:00 +0200 From: "Philippe Rochat (RSR: 318 17 93)" Reply-To: prochat@lbdsun.epfl.ch Organization: SIRANAU X-Mailer: Mozilla 4.05 [en] (X11; I; SunOS 5.5.1 sun4m) MIME-Version: 1.0 To: pierre@desertmoon.com, pgsql-general@postgresql.org, pgsql-sql@postgresql.org Subject: Re: Triggers and SQL functions References: <19981016211726.2888.qmail@desertmoon.com> Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit 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