Forum OpenACS Development: Response to Package Porting

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 😊