Thread: Do Stored Procedures exist (Besides FUNCTIONs)

Do Stored Procedures exist (Besides FUNCTIONs)

From
"Art Nicewick"
Date:
I'm researching porting an Oracle Application to PostgreSQL. I used ora2pg
and converted the DDL. However, the Oracle procedures is another issues.

It seems the all the discussions in the ORACLE TO PGSQL port document are
all about converting FUNCTIONs to FUNCTIONs, Procedures to FUNCTIONs. What
about application that already return multiple parameters from a stored
procedure.

If a program current executes a Oracle stored procedure through the
following command.

   execute storedProc (InParm1,InParm2,OutParm3,OutParm4)
   System.out.println(OutParm3 + " " + OutParm4);

with the proc being something like

   Create Procedure( InParm1,InParm2,OutParm3) is
     Begin  OutParm3 = InParm1 + InParm2;
                              OutParm4 = InParm1 * InParm2;
     Insert into TableX values (InParm1,InParm2,OutParm3,OutParm4);
     End ;

How would I do this in PL/pgsql ??? (Both Statements the Execute and the
Proc).

P.S. I cannot find any reference to a CALL or EXECUTE command in postgeSQL
.... Am I missing something ??.


---------------------------------------------------------------------------------------------------------

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


Re: Do Stored Procedures exist (Besides FUNCTIONs)

From
"Josh Berkus"
Date:
Art,

> It seems the all the discussions in the ORACLE TO PGSQL port document
>  are
> all about converting FUNCTIONs to FUNCTIONs, Procedures to FUNCTIONs.
>  What
> about application that already return multiple parameters from a
>  stored
> procedure.

This is an area currently under development for PostgreSQL.  There is
 some limited ability to return recordsets from Postgres using cursor
 and/or Record objects.  However, I do not know that this is yet
 documented, as the functionality was just added for 7.2.

Overall, stored procedures like you describe remain on the "To Do"
 list.

-Josh Brkus


Re: Do Stored Procedures exist (Besides FUNCTIONs)

From
"Josh Berkus"
Date:
Art,

> 1. ability to call a function (Like a store procedure call) ?

SELECT function_name(params);

> 2. Ability to return multiple values  ?
>
> Are both of these on the TODO list ?.

Just #2.  And, as I said, there is some limited ability to return
 mutliple values now.  I just don't know how to use it.  Hopefully
 another developer will speak up.

If, however, you're looking to post Oracle stored procedures unaltered,
 with multiple output params or returning recordsets straight to the
 buffer, don't expect it until Postgres 7.5 at the earliest.

-Josh Berkus

Re: Do Stored Procedures exist (Besides FUNCTIONs)

From
"Art Nicewick"
Date:
I guess I should have made this two questions . . . . I got your answers
(On the TODO list). I was wondering if that was true for both issues:

1. ability to call a function (Like a store procedure call) ?

2. Ability to return multiple values  ?

Are both of these on the TODO list ?.





                    "Josh Berkus"
                    <josh@agliodb        To:     "Art Nicewick" <art.nicewick@ams.com>, Masse Jacques
                    s.com>               <jacques.masse@bordeaux.cemagref.fr>
                                         cc:     pgsql-novice@postgresql.org
                    03/04/02             Subject:     Re: [NOVICE] Do Stored Procedures exist (Besides
                    03:11 PM             FUNCTIONs)






Art,

> It seems the all the discussions in the ORACLE TO PGSQL port document
>  are
> all about converting FUNCTIONs to FUNCTIONs, Procedures to FUNCTIONs.
>  What
> about application that already return multiple parameters from a
>  stored
> procedure.

This is an area currently under development for PostgreSQL.  There is
 some limited ability to return recordsets from Postgres using cursor
 and/or Record objects.  However, I do not know that this is yet
 documented, as the functionality was just added for 7.2.

Overall, stored procedures like you describe remain on the "To Do"
 list.

-Josh Brkus






Re: Do Stored Procedures exist (Besides FUNCTIONs)

From
"Joshua b. Jore"
Date:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Mon, 4 Mar 2002, Josh Berkus wrote:
> > 2. Ability to return multiple values  ?
> >
> > Are both of these on the TODO list ?.
>
> Just #2.  And, as I said, there is some limited ability to return
>  mutliple values now.  I just don't know how to use it.  Hopefully
>  another developer will speak up.

I've used the 'SETOF' token to get my functions to return multiple single
attribute tuples. There is some support for returning multiple attributes
but it didn't work well for me so I don't use it.

For the record:

CREATE TABLE bran(
    flake integer,
    fiber integer
);
INSERT INTO bran (1,2);
INSERT INTO bran (2,3);
INSERT INTO bran (3,4);

1 x 1
CREATE FUNCTION blah() RETURNS integer AS '
    SELECT flake FROM bran;
' LANGUAGE 'sql';

returns a single row

1 x n
CREATE FUNCTION blan() RETURNS SETOF integer AS '
    SELECT flake FROM bran;
' LANGUAGE 'sql';

returns all the

n x 1
CREATE FUNCTION blah() RETURNS bran AS '
    SELECT * FROM bran;
' LANGUAGE 'sql';

Returns an oid, use the projection syntax to get the individual
attributes:

SELECT flake(blah()), fiber(blah());


n x n
CREATE FRUNCTION blah() RETURNS SETOF bran AS '
    SELECT * FROM bran;
' LANGUAGE 'sql';

SELECT flake(blah()), fiber(blah());

Joshua b. Jore
http://www.greentechnologist.org

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (OpenBSD)
Comment: For info see http://www.gnupg.org

iD8DBQE8g/jtfexLsowstzcRAmHTAKCaZjZ3OOMVxUo0yiHU0cd9on7PFgCg7VL4
P5l42NyWNSlpOPI+a2a4GBA=
=ed42
-----END PGP SIGNATURE-----


Re: Do Stored Procedures exist (Besides FUNCTIONs)

From
Clinton Adams
Date:
At 12:11 PM 3/4/2002 -0800, Josh Berkus wrote:
>Art,
>
> > It seems the all the discussions in the ORACLE TO PGSQL port document
> >  are
> > all about converting FUNCTIONs to FUNCTIONs, Procedures to FUNCTIONs.
> >  What
> > about application that already return multiple parameters from a
> >  stored
> > procedure.
>
>This is an area currently under development for PostgreSQL.  There is
>  some limited ability to return recordsets from Postgres using cursor
>  and/or Record objects.  However, I do not know that this is yet
>  documented, as the functionality was just added for 7.2.


Cursor and record objects, eh?  What is the return type for those?

/clinton


>Overall, stored procedures like you describe remain on the "To Do"
>  list.
>
>-Josh Brkus
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)