X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id B9DF73A3F29 for ; Wed, 19 Jan 2005 12:35:05 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 77963-06 for ; Wed, 19 Jan 2005 12:34:51 +0000 (GMT) Received: from agems2.state.ky.us (agems2.state.ky.us [162.114.80.122]) by svr1.postgresql.org (Postfix) with ESMTP id 232EB3A3D0C for ; Wed, 19 Jan 2005 12:34:51 +0000 (GMT) Received: by agems2.state.ky.us with Internet Mail Service (5.5.2657.72) id ; Wed, 19 Jan 2005 07:34:49 -0500 Message-ID: <06AF099D94D33B4D9120504521D6539D02903CCB@agency33.state.ky.us> From: RobertD.Stewart@ky.gov To: robert.koepferl@sonorys.at, pgsql-sql@postgresql.org Subject: Re: Looking for examples of S/P Date: Wed, 19 Jan 2005 07:34:39 -0500 MIME-Version: 1.0 X-Mailer: Internet Mail Service (5.5.2657.72) Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.21 tagged_above=0 required=5 tests=AWL, NO_REAL_NAME X-Spam-Level: X-Archive-Number: 200501/144 X-Sequence-Number: 19932 I use this stored procedure to insert data into tables from my web = page. I call it using=20 select = insert_masteraccount($1,$,2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13); CREATE OR REPLACE FUNCTION insert_masteraccount("varchar", "varchar", "varchar", "varchar", "varchar", "varchar", "varchar", "varchar", = "varchar", "varchar", "varchar", "varchar", "varchar") RETURNS "varchar" AS ' DECLARE dhcp varchar:=3D\'DHCP\'; rtype varchar:=3D\'RAS\'; pass varchar:=3D\'Password\'; pool varchar:=3D\'Pool-Name\'; class varchar:=3D\'Class\'; ip varchar:=3D\'Framed-IP-Address\'; BEGIN insert into masteraccount(fname,midint,lname,username,atype) values($1,$2,$3,$4,$5); insert into passwd(u_id,currentpwd) values((select max(u_id) from masteraccount where username=3D$4),$6); insert into ipinfo(u_id,ipaddress,atype,phone_num,billing,groupname,poolname) values((select max(u_id) from masteraccount where username=3D$4),$7,$5,$10,$11,$12,$13); insert into userinfo(u_id,agency,user_email) values((select max(u_id) = from masteraccount where username=3D$4),$8,$9); insert into radcheck(username,attribute,value) values($4,pass,$6); if $7 !=3D dhcp then insert into radreply(username,attribute,value) values($4,ip,$7); else insert into radcheck(username,attribute,value) values($4,pool,$13); end if; return masteraccount.username where masteraccount.username=3D$4; END; ' LANGUAGE 'plpgsql' VOLATILE; This is one that watches my ipinfo table and move data around for me = and is setup with as trigger function CREATE OR REPLACE FUNCTION ipinfo_trg() RETURNS "trigger" AS 'DECLARE dhcp varchar:=3D\'DHCP\'; rtype varchar:=3D\'RAS\'; pool varchar:=3D\'Pool-Name\'; BEGIN if NEW.ipaddress !=3D dhcp then if OLD.ipaddress !=3D dhcp then if OLD.atype !=3D rtype then insert into vpnip(ipaddress) values(inet(OLD.ipaddress)); =09 else =09 insert into rasip(ipaddress) values(inet(OLD.ipaddress)); =09 end if; else end if; else if OLD.ipaddress !=3D dhcp then if OLD.atype !=3D rtype then insert into vpnip(ipaddress) values(inet(OLD.ipaddress)); else =09 insert into rasip(ipaddress) values(inet(OLD.ipaddress)); end if; else end if; END IF; Return NEW; END; ' LANGUAGE 'plpgsql' VOLATILE; -----Original Message----- From: K=D6PFERL Robert [mailto:robert.koepferl@sonorys.at]=20 Sent: Wednesday, January 19, 2005 4:03 AM To: pgsql-sql@postgresql.org Subject: [SQL] Looking for examples of S/P In order to learn SQL-Stored Procedure techniqes I'm looking for a = series of examples. Where can I find examples of SQL and PL/pgSQL based stored procedures? Or any of you who wants to donate some? ---------------------------(end of = broadcast)--------------------------- TIP 8: explain analyze is your friend