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
_______________________________________________
Spacewalk-devel mailing list
Spacewalk-devel@redhat.com
https://www.redhat.com/mailman/listinfo/spacewalk-devel
