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
<mailto:muhammad.farr...@enterprisedb.com>> wrote:
On Thu, Feb 12, 2009 at 4:26 PM, Gurjeet Singh
<gurjeet.si...@enterprisedb.com
<mailto: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 <mailto: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