Great!

I added a link in the tasks page ( https://fedorahosted.org/spacewalk/wiki/PostgresTasks ) and added some description.

George, please add to the project plan "Tasks and Milestones".

Muhammad Farrukh wrote:
I've created a new wiki page to track the procedures/functions migration:
https://fedorahosted.org/spacewalk/wiki/PgportProcedures

Please update the wiki page if you have worked or are working on any.

Regards,
Farrukh



On Tue, Feb 17, 2009 at 3:26 PM, Muhammad Farrukh <muhammad.farr...@enterprisedb.com <mailto:muhammad.farr...@enterprisedb.com>> wrote:



    On Thu, Feb 12, 2009 at 4:26 PM, Gurjeet Singh
    <gurjeet.si...@enterprisedb.com
    <mailto:gurjeet.si...@enterprisedb.com>> wrote:

        I am using upper case to highlight the usage of keywords. We
        otherwise should not be using uppercase in this porting effort.

        .) Datatypes:

            We follow the same rules for converting datatypes that we
        followed in porting tables.
            number => numeric (for now, until we convert tables' number
        columns to bigints as agreed)
            varchar2 => varchar
            date => timestamp
            blob, long, long raw, ... => bytea
            clob, long varchar, ...  => text
.) PROCEDURE

            Procedure keyword is not supprted in Postgres. So we should
        convert all PROCEDUREs to FUNCTIONs with RETURNS VOID, like so:

            create or replace function procedure_example(arg1 datatype,
        ...) RETURNS VOID as $$ ... $$ language plpgsql;

        .) Function without parameters

            Oracle does not require the pair of parantheses if there's
        no parameter to the procedure/function. But Postgres requires
        them, so do it like so:
create function f return int as begin return 10; end; TO create function f() returns int as $$ begin return 10; end;
        $$ language plpgsql;
.) RETURN clause

            As you must have noticed in the above example, Postgres uses
        RETURNS keyword to designate the return datatype of a function
        as opposed to RETURN keyword used by Oracle.
.) IS vs. AS

            In Oracle, AS and IS keywords can be used interchangeably to
        denote the satrt of the function/procedure code. But in
        Postgres, only AS keyword should be used.

        .) DECLARE section

            In Oracle, the first DECLARE section can be started without
        the DECLARE keyword, since the block between AS and the BEGIN
        keywords is treadted so. In plpgsql, the first DECLARE section
        must be introduced explicitly.
$$
            DECLARE
              a int;
            BEGIN
              return a := 10;
            end;
            $$

        .) EXECUTE IMMEDIATE

            For the cases where the plsql code is just executing command
        embedded in a string, simply drop the IMMEDIATE keyword.

            EXECUTE IMMEDIATE 'Truncate Table rhnOrgErrataCacheQueue';

            TO

            execute 'Truncate Table rhnOrgErrataCacheQueue';

        .) EXCEPTIONS

            There are direct mappings for most of the PLSQL named
        exceptions. Please refer to
        http://www.postgresql.org/docs/8.1/static/errcodes-appendix.html
DUP_VAL_ON_INDEX => UNIQUE_VIOLATION

        .) WHEN_NO_DATA_FOUND

            In Oracle, this exception is used to detect if a SELECT
        failed to fetch any data. It is not possible in Postgres 8.1 (it
        has been remedied since version 8.2).

            In Postgres 8.1, we have to check the FOUND variable instead.
<Oracle code> declare
                arch_type_id number;
        begin
                select id into arch_type_id from rhnArchType where label
        = label_in;
                return arch_type_id;
        exception
                when no_data_found then
rhn_exception.raise_exception('arch_type_not_found');
        end;
        </Oracle code>
        TO
        <Postgres code>
        declare
                arch_type_id numeric;
        begin
                select id into arch_type_id from rhnArchType where label
        = label_in;

                if not FOUND then
                    perform
        rhn_exception.raise_exception('arch_type_not_found');
                end if;

                return arch_type_id;
        end;
        </Postgres code>
.) CURSORs

            Please see
        http://www.postgresql.org/docs/8.1/static/plpgsql-cursors.html
        for detailed cursor usage description.
In short, convert cursors as follows: <Oracle code>
                cursor systems is
                        select  s.element id
                        from    rhnSet s
                        where   s.user_id = user_id_in
                                and s.label = 'system_list';
        </Oracle code>
        <Postgres code>
                systems cursor for
                        select  s.element id
                        from    rhnSet s
                        where   s.user_id = user_id_in
                                and s.label = 'system_list';
        </Postgres code>

        .) DETRMINISTIC keyword should be converted to STABLE like so:

        create function.... $$ begin ... 4nd; $$ language plpgsql STABLE;

        .) Executing procedures

            When executing a procedure (a function without a return
        datatype) or when performing a SELECT without INTO clause or
        whose result is not being captured into a local variable, use
        the PERFORM keyword.
<Oracle code>
            exception
                when no_data_found then
rhn_exception.raise_exception('arch_type_not_found');

        </Oracle code>
        <Postgres code>
            exception
                when no_data_found then
                        PERFORM
        rhn_exception.raise_exception('arch_type_not_found');
        </Postgres code>

        .) SHOW ERROR and /
            In Postgres the ERRORs encountered in creating a plpgsql
        using are shown immediately; so we don't need to port SHOW
        ERROR. Just get rid of it.
            Also, psql (postgres CLI client) automatically detects end
        of function code, so the / (slash) after each function/procedure
        is not needed either.

        .) AUTONOMOUS TRANSACTIONS
        Remove the COMMIT staments; Oracle requires that an
        AUTONOMOUS_TRANSACTION be explicitly COMMITted, else it will be
        rolled back. This does not apply to Postgres.

        And example of porting fllows:
        Oracle version:
        ===============
        create or replace function del( a int, b char, c varchar )
        return int as
            pragma autonomous_transaction;
        begin
            return 10;
            commit;
        end;
        /

        Postgres version:
        =================

        /* the actual function should be renamed */
        create or replace function
        del_autonomous( a int, b char, c varchar)
        returns int as $$
        begin
            return 10;
        end;
        $$ language plpgsql;

        /* now create a wrapper for the autonomous version */
        create or replace function
        del( a int, b char, c varchar )
        returns int as $$
        declare
            ret int;
        begin
            select retcode
            into ret
            from dblink( 'dbname='||current_database(),
                        'select del_autonomous( '
                        || coalesce( a::varchar, 'null' ) || ', '
                        || coalesce( quote_literal( b ), 'null' ) || ', '
                        || coalesce( quote_literal( c ), 'null' ) ||
                        ' )' )
                        as f( retcode int );

            return ret;
        end;
        $$ language plpgsql;


        .) OUT parameters

        (no concrete/clear implementation details yet; am in
        consultation with PG community)


    For tackling OUT parameters we can go with one of the below
    possibilities:

    1. Use functions for postgres and use RETURN keyword instead of OUT
    to obtain the desired results. This would require changes in
    application and every procedure call (with OUT parameters) would
    have two code paths; one for Oracle and One for Postgres in a
    fashion similar to below:

        if Oracle then
                Oracle procedure call statement
        else if postgres
               Postgres function call statement
       end if;

    And more changes might also be required to handle/take care of the
    function return value.


    2. As mentioned above there is only one OUT parameter in all the
    procedures and they can easily be converted to functions with return
    values to get the results of OUT parameter. We can change those
    procedures for both Oracle and Postgres to behave the same way i.e.
    using RETURN values. This would provide similar calling code from
    application for both Oracle and Postgres. This would/might also
    require changes in the application but according to my understanding
    the changes would be minimal as compared to CASE1 and the
    application won't require separate codes for calling Oracle and
    Postgers functions.


    The second approach seems more reasonable and the procedures would
    be converted using this approach; this would apply to only the
    procedures with OUT parameters and the number of procedures being
    affected is around 15.

    Regards,
    Farrukh





-- gurjeet[.sin...@enterprisedb.com
        EnterpriseDB      http://www.enterprisedb.com

        singh.gurj...@{ gmail | hotmail | indiatimes | yahoo }.com


        _______________________________________________
        Spacewalk-devel mailing list
        Spacewalk-devel@redhat.com <mailto:Spacewalk-devel@redhat.com>
        https://www.redhat.com/mailman/listinfo/spacewalk-devel




------------------------------------------------------------------------

_______________________________________________
Spacewalk-devel mailing list
Spacewalk-devel@redhat.com
https://www.redhat.com/mailman/listinfo/spacewalk-devel

_______________________________________________
Spacewalk-devel mailing list
Spacewalk-devel@redhat.com
https://www.redhat.com/mailman/listinfo/spacewalk-devel

Reply via email to