pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: RobertD.Stewart@ky.gov
To: robert.koepferl@sonorys.at
To: pgsql-sql@postgresql.org
Subject: Re: Looking for examples of S/P
Date: Wed, 19 Jan 2005 07:34:39 -0500
Message-ID: <06AF099D94D33B4D9120504521D6539D02903CCB@agency33.state.ky.us> (raw)

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



view thread (4+ messages)  latest in thread

Message-ID: <06AF099D94D33B4D9120504521D6539D02903CCB@agency33.state.ky.us>
Permalink:  ../06AF099D94D33B4D9120504521D6539D02903CCB@agency33.state.ky.us/
Also on:    postgresql.org/message-id/06AF099D94D33B4D9120504521D6539D02903CCB@agency33.state.ky.us

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: RobertD.Stewart@ky.gov, robert.koepferl@sonorys.at
  Subject: Re: Looking for examples of S/P
  In-Reply-To: <06AF099D94D33B4D9120504521D6539D02903CCB@agency33.state.ky.us>
  References: <06AF099D94D33B4D9120504521D6539D02903CCB@agency33.state.ky.us>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox