Re: Looking for examples of S/P - Mailing list pgsql-sql
From | RobertD.Stewart@ky.gov |
---|---|
Subject | Re: Looking for examples of S/P |
Date | |
Msg-id | 06AF099D94D33B4D9120504521D6539D02903CCB@agency33.state.ky.us Whole thread Raw |
In response to | Looking for examples of S/P (KÖPFERL Robert <robert.koepferl@sonorys.at>) |
List | pgsql-sql |
I use this stored procedure to insert data into tables from my web page. I call it using 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:=\'DHCP\'; rtype varchar:=\'RAS\'; pass varchar:=\'Password\'; pool varchar:=\'Pool-Name\'; class varchar:=\'Class\'; ip varchar:=\'Framed-IP-Address\'; BEGINinsert 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=$4),$6);insert into ipinfo(u_id,ipaddress,atype,phone_num,billing,groupname,poolname) values((select max(u_id) from masteraccount where username=$4),$7,$5,$10,$11,$12,$13);insert into userinfo(u_id,agency,user_email) values((select max(u_id) from masteraccount where username=$4),$8,$9);insert into radcheck(username,attribute,value) values($4,pass,$6);if $7 != 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=$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:=\'DHCP\'; rtype varchar:=\'RAS\'; pool varchar:=\'Pool-Name\'; BEGIN if NEW.ipaddress != dhcp thenif OLD.ipaddress != dhcp then if OLD.atype != rtype then insert into vpnip(ipaddress) values(inet(OLD.ipaddress)); else insert into rasip(ipaddress) values(inet(OLD.ipaddress)); end if;else end if; else if OLD.ipaddress != dhcp then if OLD.atype != rtype then insert into vpnip(ipaddress) values(inet(OLD.ipaddress)); else 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ÖPFERL Robert [mailto:robert.koepferl@sonorys.at] 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