Re: callable statement please

Postgresql does not have 'execute' or 'call' functionality. I heard it as
on the TO-DO list. I have a funky work-around. This seems to work, but I
have not benchmarked it. I would like 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 network call and cycles.
---------------------------------------------------------------------------------------------------------

Arthur Nicewick
American Management Systems
Corporate Technology Group
art_nicewick(at)ams(dot)com
(703) 267-8569

Quote of the week:
"Walking on water and developing software from a specification are easy if
both are frozen."
-- Edward V. Berard, "Life-Cycle Approaches"