From: | "Art Nicewick" <art(dot)nicewick(at)ams(dot)com> |
---|---|
To: | tfo(at)monsterlabs(dot)com |
Cc: | pgsql-admin(at)postgresql(dot)org, pgsql-general(at)postgresql(dot)org, pgsql-general-owner(at)postgresql(dot)org |
Subject: | [GENERAL] Tricking postgresql to do a oracle-like stored Procedure |
Date: | 2002-03-05 23:12:40 |
Message-ID: | OF9A4E338B.F15A4D33-ON85256B73.006BAF7E@ams.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin pgsql-general |
I have this idea to emulate a stored procedure in PostgreSQL. It seems to
work, but I have not benchmarked it. I decided to post a message out here
to see what people think about the idea.
First of all "THIS IS A WORKAROUND" , I AM NOT PROPOSING A FIX ...
O.K., Here's the idea ..
I want to simulate the oracle call exec
sp1(INPARM1,INPARM2,OUTPARM1,OUTPARM2);
-- Say have a simple Oracle Stored Procedure ... like so:
procedure sp1 (iparm1 IN integer, oparm2 OUT integer, oparm3 OUT) as
begin
IF (iparm1 <> 1) then
oparm2 := 172;
oparm3 := 201;
endif
end:
.
. - - -- > execute sp1(0,:outParm2,:outParm3)
with Postgresql we can do this
Create Temporary table table_with_SP1_Name ( inparm1 integer, outparm2
integer, outparm3 integer );
create function SP1(integer, integer,integer) returns integer as '
begin
delete from table_with_SP1_Name ;
if ($1 <> 1) then
insert into table_with_SP1_Name ($1,172,201);
endif;
end; ' language 'plpgsql';
then I would have to use two statements to execute the stored procedure ...
----> select sp1(0,:outParm2,:Outparm3);
select inparm2, outparm2, outparm3 into :inparm2, :outparm2,
:outparm3 from table_with_SP1_name ;
If the temp table is memory only, then my only major overhead would be the
additional SQL request. ...
---------------------------------------------------------------------------------------------------------
Arthur Nicewick
American Management Systems
Corporate Technology Group
art_nicewick(at)ams(dot)com
(703) 267-8569
Quote of the week:
"Computers in the future may weigh no more than 1.5 tons."
Popular Mechanics, forecasting the relentless march of science, 1949
tfo(at)monsterlabs(dot)com
Sent by: To: pgsql-general(at)postgresql(dot)org
pgsql-general-owner(at)post cc: pgsql-admin(at)postgresql(dot)org
gresql.org Subject: [GENERAL] psql \df and opaque functions
03/05/02 01:39 PM
is it a known issue that \df in psql does not list functions that return
opaque (i.e., those used in triggers)?
also, does \dd only list builtins? if so, is there a convenient way to
view triggers in psql?
-tfo
p.s. i've looked through the docs, to no avail, and would've checked
the archives, but search seems to be broken, at the moment.
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
From | Date | Subject | |
---|---|---|---|
Next Message | Linda Fousek | 2002-03-05 23:58:01 | FATAL ERROR: database does not exist in the system catalog |
Previous Message | tfo | 2002-03-05 18:39:52 | psql \df and opaque functions |
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2002-03-05 23:33:05 | Re: Database quota |
Previous Message | Andrew Bartley | 2002-03-05 23:07:29 | Re: FATAL 2: RelationPutHeapTuple: failed to add tuple |