[SQL] stored procedures in postgresql user plpgsql
I know that there are not stored procedures like that in oracle. I know that you have to create functions. I have tried to create a simple function to select data from a table. Could you all please correct me Thanks CREATE FUNCTION retrive_atype(varchar) RETURNS varchar AS ' BEGIN RETURN select username from masteraccount where atype=$1; END; ' LANGUAGE 'plpgsql'; To call the function I used Select retrive_atype(); Please help Robert Stewart Network Eng Commonwealth Office of Technology Finance and Administration Cabinet 101 Cold Harbor Work # 502 564 9696 Cell # 502 330 5991 Email [EMAIL PROTECTED]
Re: [SQL] Table History
Here is a example of a trigger function CREATE OR REPLACE FUNCTION public.ipinfo_trg() RETURNS trigger AS 'DECLARE dhcpvarchar:=\'DHCP\'; rtype varchar:=\'RAS\'; 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; Here is a example of how to call the trigger function from your table CREATE TRIGGER update_ipinfo_trg AFTER UPDATE ON public.ipinfo FOR EACH ROW EXECUTE PROCEDURE public.ipinfo_trg(); -Original Message- From: John DeSoi [mailto:[EMAIL PROTECTED] Sent: Friday, December 17, 2004 10:38 AM To: Richard Sydney-Smith Cc: [EMAIL PROTECTED] Subject: Re: [SQL] Table History On Dec 17, 2004, at 1:23 AM, Richard Sydney-Smith wrote: > I expect this has been done MANY times and I wonder if a general > purpose trigger exists or if not then can someone point me to an > example set of triggers? I'm not aware of a "general purpose" trigger for this. If you just want some extra trigger examples other than what is in the documentation, there is a test file in the distribution with quite a few: src/test/regress/sql/plpgsql.sql Best, John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly ---(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
Re: [SQL] Looking for examples of S/P
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
dhcpvarchar:=\'DHCP\';
rtype varchar:=\'RAS\';
passvarchar:=\'Password\';
poolvarchar:=\'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
dhcpvarchar:=\'DHCP\';
rtype varchar:=\'RAS\';
poolvarchar:=\'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:[EMAIL PROTECTED]
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
Re: [WW Spam: medium] [SQL] Building a database from a flat file
Could you set up functions triggers on the temp table that would do updates and inserts on the other tables from the data entered into the temp table? Then all you would have to do is inter the data into the temp table -Original Message- From: Casey T. Deccio [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 02, 2005 7:15 PM To: pgsql-sql@postgresql.org Subject: [WW Spam: medium] [SQL] Building a database from a flat file A database I am currently using is built and updated periodically from a flat csv file (The situation is rather unfortunate, but that's all I have right now). The schema I use is more complex than the flat file, so I follow a process to populate the tables with the data from the file. First I slurp the whole file into one temporary table, whose columns correspond to the columns in the file. Then I DELETE all the existing rows from the tables in the schema and perform a series of queries on that table to INSERT and UPDATE rows in the tables that are in the schema. Then I DELETE the data from the temporary table. I do it this way, rather than trying to synchronize it, because of the inconsistencies and redundancies in the flat file. There is more than one problem with this, but the largest is that I would like to perform this whole database rebuild within one transaction, so other processes that need to access the database can do so without noticing the disturbance. However, performing this set of events (besides populating the temporary table) within a single transaction takes a long time--over an hour in some cases. What are some suggestions to help improve performance with replacing one set of data in a schema with another? Casey ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
