-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Thanks for another great guide!
Devan On Thu, 12 Feb 2009 16:56:54 +0530 Gurjeet Singh <[email protected]> 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) > > - -- Devan Goodwin <[email protected]> Software Engineer Spacewalk / RHN Satellite Halifax, Canada 650.567.9039x79267 -----BEGIN PGP SIGNATURE----- Version: GnuPG v2.0.9 (GNU/Linux) iEYEARECAAYFAkmUHTkACgkQAyHWaPV9my4iXACfWTpMXWxrt6ShyA6lObPX1iJR MrMAoLaek6nc2atdhfteozcFXjXriCtS =kBnX -----END PGP SIGNATURE----- _______________________________________________ Spacewalk-devel mailing list [email protected] https://www.redhat.com/mailman/listinfo/spacewalk-devel
