Need help in porting Oracle PL/SQL's OUT paramater based procedures

Hi All,

I am involved in porting Spacewalk <https://fedorahosted.org/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<https://www.redhat.com/archives/spacewalk-devel/2009-February/msg00123.html>),
and am now stuck with the OUT parameters! I saw the example of converting a
PL/SQL function
cs_parse_url<http://www.postgresql.org/docs/8.1/static/plpgsql-porting.html#PLPGSQL-PORTING-EX3>,
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.
--
gurjeet[(dot)singh](at)EnterpriseDB(dot)com
singh(dot)gurjeet(at){ gmail | hotmail | indiatimes | yahoo }.com

EnterpriseDB http://www.enterprisedb.com

Mail sent from my BlackLaptop device