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
