Обсуждение: Need help in porting Oracle PL/SQL's OUT paramater based procedures

Hi All,<br /><br />    I am involved in porting <a href="https://fedorahosted.org/spacewalk/">Spacewalk</a>'s backend
DBschema from Oracle to Postgres. We are almost done with table migration, and are now attempting procedure/function
porting.<br/><br />A few things have been sorted out (<a
href="https://www.redhat.com/archives/spacewalk-devel/2009-February/msg00123.html">link</a>),and am now stuck with the
OUTparameters! I saw the example of converting a PL/SQL function <a
href="http://www.postgresql.org/docs/8.1/static/plpgsql-porting.html#PLPGSQL-PORTING-EX3">cs_parse_url</a>,and see that
finallyit has been advised to use the ported version as<br /><br />SELECT * FROM cs_parse_url('<a
href="http://foobar.com/query.cgi?baz'">http://foobar.com/query.cgi?baz'</a>);<br/><br />that is, not passing anything
forthe OUT or INOUT parameters. This works fine for a simple SELECT usage, but does not play well when this function is
tobe called from another function, (and assuming that it'd break the application code too, which uses Oracle syntax of
callingfunctions)!<br /><br />I have a simple function f() which I'd like to be ported in such a way that it works when
calledfrom other plpgsql code, as well as when the application uses the Oracle like syntax. Here's a sample usage of
thefunction f() in Oracle:<br /><br /><snip><br />create or replace function f( a in out int, b out varchar )
returnchar as<br />begin<br />    a := 10;<br />    b := 'some string';<br />    return 'c';<br />end;<br />/<br /><br
/>createor replace function f_caller return int as<br />     a int;<br />    b varchar(32);<br />begin<br />   
dbms_output.put_line(f( a, b ) );<br />    dbms_output.put_line( a );<br />    dbms_output.put_line( b );<br />   
return0;<br />end;<br />/<br /><br />set serveroutput on<br /><br />select f_caller from dual;<br /><br />  F_CALLER<br
/>----------<br/>         0<br /><br />c<br />10<br />some string<br /></snip><br /><br />Has anyone attempted
portingPL/SQL, and if so, please share your experince with the OUT parameters.<br /><br />Thanks and best regards,<br
/><br/>PS: Postgres 8.1 is going to be the oldest supported version by Spacewalk.<br />-- <br
/>gurjeet[.singh]@EnterpriseDB.com<br/>singh.gurjeet@{ gmail | hotmail | indiatimes | yahoo }.com<br /><br
/>EnterpriseDB     <a href="http://www.enterprisedb.com">http://www.enterprisedb.com</a><br /><br />Mail sent from my
BlackLaptopdevice<br /> 

Gurjeet Singh wrote:
> 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:

If you really want Oracle-compatible functions I think there's a company
that might sell you a solution :-)

However, failing that you'll want an example of OUT parameters in
PostgreSQL code - see below. The main thing to remember is that the OUT
is really just a shortcut way of defining a record type that gets
returned. It's nothing like passing by reference in <insert real
programming language here>.


BEGIN;

CREATE OR REPLACE FUNCTION f1(IN a integer, INOUT b integer, OUT c
integer) RETURNS RECORD AS $$
BEGIN
    c := a + b;
    b := b + 1;
    -- No values in RETURN
    RETURN;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION f2() RETURNS boolean AS $$
DECLARE
    a integer := 1;
    b integer := 2;
    c integer := -1;
    r RECORD;
BEGIN
    r := f1(a, b);
    -- Original variables unaffected
    RAISE NOTICE 'a=%, b=%, c=%', a,b,c;
    -- OUT params are here instead
    RAISE NOTICE 'r.b=%, r.c=%', r.b, r.c;

    -- This works, though notice we treat the function as a row-source
    SELECT (f1(a,b)).* INTO b,c;
    RAISE NOTICE 'a=%, b=%, c=%', a,b,c;

    RETURN true;
END;
$$ LANGUAGE plpgsql;

SELECT f2();

ROLLBACK;

--
  Richard Huxton
  Archonet Ltd