Thread: Looking for examples of S/P

Looking for examples of S/P

From
KÖPFERL Robert
Date:
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?


Re: Looking for examples of S/P

From
Michael Fuhr
Date:
On Wed, Jan 19, 2005 at 10:03:13AM +0100, KÖPFERL Robert wrote:

> 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?

The General Bits column would be one place:

http://www.varlena.com/varlena/GeneralBits/

-- 
Michael Fuhr
http://www.fuhr.org/~mfuhr/


Re: Looking for examples of S/P

From
RobertD.Stewart@ky.gov
Date:
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


Re: Looking for examples of S/P

From
John DeSoi
Date:
On Jan 19, 2005, at 4:03 AM, KÖPFERL Robert wrote:

> 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?

The pgEdit distribution includes an extensive example for importing and
analyzing web server logs using only SQL and PL/pgSQL. Look in the
pgEdit/Examples/web folder after installation.

http://pgedit.com/download


John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL