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

On Thu, Feb 12, 2009 at 6:18 PM, Richard Huxton <dev(at)archonet(dot)com> wrote:

> 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 :-)

:) Spacewalk is not interested you see.

>
>
> 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;

You see, you did not pass the third (OUT) parameter when calling the
function:

r := f1(a, b);

This differs from Oracle syntax where you _need_ to pass the third
parameter.

And what if the Oracle function actually returns a value too? How do we
handle that in the application, because we can't declare RECORD vars in
Java/perl/python etc.

Thanks and best regards,
--
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