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

[Gurjeet Singh <singh.gurjeet@xxxxxxxxx>]

On Thu, Feb 12, 2009 at 6:18 PM, Richard Huxton <dev@xxxxxxxxxxxx> 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[.singh]@EnterpriseDB.com
singh.gurjeet@{ gmail | hotmail | indiatimes | yahoo }.com

EnterpriseDB      http://www.enterprisedb.com

Mail sent from my BlackLaptop device