Well, since the grownups are away (grownups in wisdom, that is, not necessarily age), I'll give ya my 2 cents.
A package in Oracle is a way to encapsulate procedures & functions in a single unit.
So the following Oracle package (showing only the package header) defines 2 functions and 1 procedure.
create package acs
as
function add_user (
user_id in users.user_id%TYPE default null,
...
member_state in membership_rels.member_state%TYPE default 'approved'
)
return users.user_id%TYPE;
procedure remove_user (
user_id in users.user_id%TYPE
);
function magic_object_id (
name in acs_magic_objects.name%TYPE
) return acs_objects.object_id%TYPE;
end acs;
These would be called as:
acs.add_user( ... );
acs.remove_user( ... );
acs.magic_object_id( ... );
Since postgres doesn't have a way to encapsulate functions together, we need to agree on a convention, which I think is described in the Guidelines document.
So in postgres, we would create 3 functions:
create function acs__add_user( ... ) ...
create function acs__remove_user( ... ) ...
create function acs__magic_object_id( ... ) ...
Those are 2 underscores between the "package name" and the "function name". The function would then be ported just as any oracle function/procedure would be. One of the problems is going to be figuring out how to deal with all the default parameters.
This provides understandable function names (i.e. acs_user__new() creates a new user and party__new() creates a new party) and it potentially allows a way to create an API-browser by showing a list of "packages" and then the "functions" within those packages.
A few other differences between PL/SQL and plpgsql:
- You can't use
%TYPE types in the parameters or the return var. You can use them for variables that you create in the function
- You can't reference parameter variables by prefacing them with the package/function name. For example, in many ACS functions, the use of
v_varname variables have been removed. Instead, they're using the 'varname' directly (without the v_), but distinguishing it from columns of the same name by calling the full package_name.function_name.varname. This doesn't work in pg, so we need to reinstate v_varnames.
- You can't call functions by specifying the parameter label in the call. So you can't do
acs_permission.grant_permission( object_id => v_user_id ...). Instead, you need to send parameters in the order that they're defined.
So, here's how I would redefine one acs package function:
create package body acs
as
...
< other functions & procedures >
function magic_object_id (
name in acs_magic_objects.name%TYPE
) return acs_objects.object_id%TYPE
is
object_id acs_objects.object_id%TYPE;
begin
select object_id
into magic_object_id.object_id
from acs_magic_objects
where name = magic_object_id.name;
return object_id;
end magic_object_id;
in postgres:
drop function acs__magic_object_id( varchar );
create function acs__magic_object_id ( varchar )
returns integer as '
declare
v_name alias for $1;
v_object_id acs_objects.object_id%TYPE;
begin
select into v_object_id object_id
from acs_magic_objects
where name = v_name;
return v_object_id;
end;
' language 'plpgsql';
All package calls in the tcl & sql code would then have to be changed to use these conventions.
I hope that this is what you were looking for and that the experts will liberally correct where necessary.
BTW, Happy Birthday tomorrow Don - you know that forgetting your birthday is a sure sign of age 😊
