[GENERAL] Tricking postgresql to do a oracle-like stored Procedure

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@ams.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@monsterlabs.com
 
                    Sent by:                        To:     pgsql-general@postgresql.org
 
                    pgsql-general-owner@post        cc:     pgsql-admin@postgresql.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