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> wrote: > > > On Thu, Feb 12, 2009 at 4:26 PM, Gurjeet Singh < > 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 >> https://www.redhat.com/mailman/listinfo/spacewalk-devel >> > >
_______________________________________________ Spacewalk-devel mailing list Spacewalk-devel@redhat.com https://www.redhat.com/mailman/listinfo/spacewalk-devel
