I have just pushed the following commit: # modified: main.sql # modified: procs/lookup_arch_type.sql
# new file: procs/create_pxt_session.sql # new file: procs/delete_server_bulk.sql # new file: procs/lookup_cf_state.sql # new file: procs/lookup_feature_type.sql # new file: procs/lookup_package_key_type.sql # new file: procs/lookup_package_provider.sql # new file: procs/queue_errata.sql # new file: procs/truncateCacheQueue.sql Please exclude these from your work. Jeff, if there's one, can you please share with us the dependency list of procedures as you did for tables. Thanks and best regards, On Thu, Feb 12, 2009 at 4:56 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) > > > -- > gurjeet[.sin...@enterprisedb.com > EnterpriseDB http://www.enterprisedb.com > > singh.gurj...@{ gmail | hotmail | indiatimes | yahoo }.com > > -- 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
