Since the application code and the database views calls the packaged
functions as:
call package_name.function_name()
OR
select package_name.function_name()
and since such call sites are numerous, it is desirable to not change each
and every call site. Hence the approach taken to tackle this is to create a
database schema for each package by the same name, and create package member
functions in that schema.
For clarity and to match the existing Oracle DDL files, we will not be
creating one file each for each member function. Instead, we will keep the
same heirarchy as schema/spacewalk/rhnsat/packages/ directory and utilise
the pkb (package-body) files to create all the functions.
Following are the instructions on how to port Oracle packages to postgres:
(see rhn_user package file for an example
(schema/spacewalk/postgresql/packages/rhn_user.pkb) )
.) Start with creating a schema with the same name as that of the package:
create schema package_name;
.) Alter the session setting (search_path) so that any new objects created
from this point on will be created in this new schema.
update pg_settings set setting = 'package_name,' || setting where name =
'search_path';
.) Package private variables.
I haven't seen any package private variable used in member functions. (They
are declared but not used.)
.) Package public variables
There are some instances of cursors being declared in the public part of the
package (.pks files). But I have seen these public variables being used only
in member functions, and nowhere else. So it would be safe to put these
declarations in the member functions wherever they are being used.
Since this will surely lead to duplication of code, it is suggested that we
put the package_name.variable_name in the comments in the Postgres' version
of the member functions where they are going to be used. This is so that
anybody who wishes to make a change will be able to clearly identify the
corresponding Oracle/Postgres code that needs change.
.) Convert every member function to use DDL.
...
function del(a int) return number is
b varchar2(20);
begin
-- do something
end del;
...
BECOMES:
...
create or replace
function del(a int) returns numeric as $$
declare
b varchar(20);
begin
-- do something
end;
$$ language plpgsql;
...
Please note that we are trying to retain the white-space of the original
code so that a simple diff of the Oracle and the Postgres versions of tha
package shows minimal differences.
.) Convert the functions based on the guidelines shared arlier for
procedure/function porting.
.) And at last, restore the search_path setting.
update pg_settings set setting = overlay( setting placing '' from 1 for
(length('package_name')+1) ) where name = 'search_path';
--
gurjeet[[email protected]
EnterpriseDB http://www.enterprisedb.com
singh.gurj...@{ gmail | hotmail | indiatimes | yahoo }.com
_______________________________________________
Spacewalk-devel mailing list
[email protected]
https://www.redhat.com/mailman/listinfo/spacewalk-devel
