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

Поиск
Список
Период
Сортировка

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

От
Gurjeet Singh
Дата:
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 /> 

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

От
Gurjeet Singh
Дата:
(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.
--
gurjeet[.singh]@EnterpriseDB.com
singh.gurjeet@{ gmail | hotmail | indiatimes | yahoo }.com

EnterpriseDB      http://www.enterprisedb.com

Mail sent from my BlackLaptop device

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

От
Richard Huxton
Дата:
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

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

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

EnterpriseDB      http://www.enterprisedb.com

Mail sent from my BlackLaptop device