Fwd: Need help in porting Oracle PL/SQL's OUT paramater based procedures - Mailing list pgsql-general

(forgot to include general list earlier)

Hi All,

    I am involved in porting Spacewalk's backend DB schema from Oracle to Postgres. We are almost done with table migration, and are now attempting procedure/function porting.

A few things have been sorted out (link), and am now stuck with the OUT parameters! I saw the example of converting a PL/SQL function cs_parse_url, and see that finally it has been advised to use the ported version as

SELECT * FROM cs_parse_url('http://foobar.com/query.cgi?baz');

that is, not passing anything for the OUT or INOUT parameters. This works fine for a simple SELECT usage, but does not play well when this function is to be called from another function, (and assuming that it'd break the application code too, which uses Oracle syntax of calling functions)!

I have a simple function f() which I'd like to be ported in such a way that it works when called from other plpgsql code, as well as when the application uses the Oracle like syntax. Here's a sample usage of the function f() in Oracle:

<snip>
create or replace function f( a in out int, b out varchar ) return char as
begin
    a := 10;
    b := 'some string';
    return 'c';
end;
/

create or replace function f_caller return int as
    a int;
    b varchar(32);
begin
    dbms_output.put_line( f( a, b ) );
    dbms_output.put_line( a );
    dbms_output.put_line( b );
    return 0;
end;
/

set serveroutput on

select f_caller from dual;

  F_CALLER
----------
         0

c
10
some string
</snip>

Has anyone attempted porting PL/SQL, and if so, please share your experince with the OUT parameters.

Thanks and best regards,

PS: Postgres 8.1 is going to be the oldest supported version by Spacewalk.