|
|
 | | From: | RobertD.Stewart at ky.gov | | Subject: | Re: Looking for examples of S/P | | Date: | Wed, 19 Jan 2005 07:34:39 -0500 |
|
|
 | 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\';
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=$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 then
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;
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
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
|
|
|
