Forum OpenACS Development: acs_object_type__create_type

The key things going on here are:

• The PG equivalent of the Oracle function package.function() is package__function()
• PL/PGSQL doesn't support keyword arguments or default values.

The way I go about it is to look up the PG definition of the function in question (in this case, acs-kernel/sql/postgresql/acs-metadata-create.sql):

create function acs_object_type__create_type (varchar,varchar,varchar,varchar,varchar,varchar,varchar,boolean,varchar,varchar)
returns integer as '
declare
  create_type__object_type            alias for $1;
  create_type__pretty_name            alias for $2;
  create_type__pretty_plural          alias for $3;
  create_type__supertype              alias for $4;
  create_type__table_name             alias for $5;
  create_type__id_column              alias for $6;  -- default ''XXX''
  create_type__package_name           alias for $7;  -- default null
  create_type__abstract_p             alias for $8;  -- default ''f''
  create_type__type_extension_table   alias for $9;  -- default null
  create_type__name_method            alias for $10; -- default null
  v_package_name acs_object_types.package_name%TYPE;
  v_name_method                       varchar;
  v_idx                               integer;
begin
  ...

and with one eye on that and the other on the function call to be ported, step through the parameters of the PG function and shift the parameters of the function call so they're in the same order. Where a parameter is missing, substitute the default value (we're lucky in this case - the PG function definition has the default values listed as comments. Without these, you need a third eye to look through the original Oracle function definition to get the defaults)

That's the bulk of it. The other thing to do here is decide how to convert the begin / end block. There are two ways:

• Put a semicolon after the begin and put a select in front of the function calls
• Put the whole block into a temporary function and select() and drop it afterwards.

The first way gives us:

begin;

    select acs_object_type__create_type (
        'acs_message',
        'Message',
        'Messages',
        'content_item',
        'ACS_MESSAGES',
        'MESSAGE_ID',
        null,
        'f',
        null,
        'ACS_MESSAGE.NAME'
    );

    select acs_object_type__create_type (
        'acs_message_revision',
        'Message Revision',
        'Message Revisions',
        'content_revision',
        'CR_REVISIONS',
        'REVISION_ID',
        null,
        'f',
        null,
        'ACS_OBJECT.DEFAULT_NAME'
    );

end;

The second way gives us:

create function inline_0 ()
returns integer as '
begin

    perform acs_object_type__create_type (
        ''acs_message'',
        ''Message'',
        ''Messages'',
        ''content_item'',
        ''ACS_MESSAGES'',
        ''MESSAGE_ID'',
        null,
        ''f'',
        null,
        ''ACS_MESSAGE.NAME''
    );

    perform acs_object_type__create_type (
        ''acs_message_revision'',
        ''Message Revision'',
        ''Message Revisions'',
        ''content_revision'',
        ''CR_REVISIONS'',
        ''REVISION_ID'',
        null,
        ''f'',
        null,
        ''ACS_OBJECT.DEFAULT_NAME''
    );

    return 0;

end;' language 'plpgsql';

select inline_0 ();
drop function inline_0 ();

Note embedded single quotes must be escaped, and you can use perform instead of select because we're inside a PL/PGSQL function and aren't interested in the function return value.

Other interesting stuff: acs_object_type__create_type will convert the name_method to PG syntax for you (eg. ACS_OBJECT.DEFAULT_NAME will become ACS_OBJECT__DEFAULT_NAME)

Hope that helps