Here's another proposal for porting ACS 4.0 PL/SQL packages to PG:
For each function/procedure contained within a PL/SQL package
we write pltcl or plpgsql function named as
<package_name>__<function_name>.
Sometimes function_name can be abbreviated when it's
obvious from package_name, e.g. acs_object_type__create
instead of acs_object_type__create_type.
However, we must take care of the following:
- not to pass NULL as one of the arguments, because function
will simply return NULL
- whenever dynamic sql is required, we are forced to use pltcl
(is this correct?)
- whenever we want to _return_ NULL, we must resort to plpgsql
(correct?)
So, here are the simple ones:
create function acs_attribute__add_description(text, text, text, text) returns text as '
declare
v_object_type alias for $1;
v_attribute_name alias for $2;
v_description_key alias for $3;
v_description alias for $4;
begin
insert into acs_attribute_descriptions
(object_type, attribute_name, description_key, description)
values
(v_object_type, v_attribute_name,
v_description_key, v_description);
end;
return v_description;
' language 'plpgsql';
create function acs_attribute__drop_description(text, text, text) returns text as '
declare
v_object_type alias for $1;
v_attribute_name alias for $2;
v_description_key alias for $3;
begin
delete from acs_attribute_descriptions
where object_type = v_object_type
and attribute_name = v_attribute_name
and description_key = v_description_key;
end;
return v_description_key;
' language 'plpgsql';
Now, here's the complicated one. Once again, I'll try to find a
workaround to Oracle's default value handling. Needless to say,
it's ugly, again. I've created a simple pltcl function:
create function arg_pack(text, text) returns text as '
return "[list $1 $2] "
' language 'pltcl' ;
So that Oracle's PL/SQL is tranformed in following way:
Oracle:
attr_id := acs_attribute.create_attribute(
object_type => 'acs_object',
attribute_name => 'object_type',
datatype => 'string',
pretty_name => 'Object Type',
pretty_plural => 'Object Types'
);
Postgres:
select acs_attribute__create(
arg_pack('object_type', 'acs_object') ||
arg_pack('attribute_name', 'object_type') ||
arg_pack('datatype', 'string') ||
arg_pack('pretty_name', 'Object Type') ||
arg_pack('pretty_plural', 'Object Types')
);
arg_pack is used to construct key/value pairs
into
array-like lists which could be then parsed from within
plpgsql function, using
arg_unpack:
create function arg_unpack(text, text, text) returns text as '
declare
packed_args alias for $1;
argument_name alias for $2;
default_value alias for $3;
begin
if ... NULL indicator is set ... then
return NULL;
else
if ... the argument is supplied ... then
return argument_value;
else
return default_value;
end if;
end if;
end;
' language 'plpgsql' ;
arg_unpack accepts 3 arguments: the first one is packed argument
list ready to be fed into
array set, the second is the
argument name (
key) and the third is default value which is returned
if
key does not appear in packed argument list. The function
is written in plpgsql so we can return genuine NULL from it.
Yes, but how can we explicitly set one named argument to NULL?
Since we cannot pass NULL value to arg_pack (because complete
packed argument list would be NULL-ified), we denote NULL argument
by setting its "NULL indicator", i.e. another argument named
<argument_name>.null to arbitrary value:
select acs_object_type__create(
arg_pack('object_type', 'acs_object') ||
arg_pack('supertype.null', 'yes') ||
arg_pack('pretty_name' , 'Object') ||
arg_pack('pretty_plural' , 'Objects') ||
arg_pack('table_name' , 'acs_objects') ||
arg_pack('id_column' , 'object_id') ||
arg_pack('package_name' , 'acs_object') ||
arg_pack('name_method' , 'acs_object__default_name')
);
Let's see how
acs_object_type__create handles this:
create function acs_object_type__create(text) returns text as '
declare
packed_args alias for $1;
v_object_type acs_object_types.object_type%TYPE;
v_pretty_name acs_object_types.pretty_name%TYPE;
v_pretty_plural acs_object_types.pretty_plural%TYPE;
v_supertype acs_object_types.supertype%TYPE;
v_table_name acs_object_types.table_name%TYPE;
v_id_column acs_object_types.id_column%TYPE;
v_package_name acs_object_types.package_name%TYPE;
v_abstract_p acs_object_types.abstract_p%TYPE;
v_type_extension_table acs_object_types.type_extension_table%TYPE;
v_name_method acs_object_types.name_method%TYPE;
begin
select
arg_unpack(packed_args, ''object_type''),
arg_unpack(packed_args, ''pretty_name''),
arg_unpack(packed_args, ''pretty_plural''),
arg_unpack(packed_args, ''supertype'', ''acs_object''),
arg_unpack(packed_args, ''table_name''),
arg_unpack(packed_args, ''id_column''),
arg_unpack(packed_args, ''package_name''),
arg_unpack(packed_args, ''abstract_p'', ''f''),
arg_unpack(packed_args, ''type_extension_table''),
arg_unpack(packed_args, ''name_method'')
into
v_object_type,
v_pretty_name,
v_pretty_plural,
v_supertype,
v_table_name,
v_id_column,
v_package_name,
v_abstract_p,
v_type_extension_table,
v_name_method;
if v_package_name is null then
v_package_name := v_object_type;
end if;
insert into acs_object_types
(object_type, pretty_name, pretty_plural, supertype, table_name,
id_column, abstract_p, type_extension_table, package_name,
name_method)
values
(v_object_type, v_pretty_name, v_pretty_plural, v_supertype, v_table_name,
v_id_column, v_abstract_p, v_type_extension_table, v_package_name,
v_name_method);
return v_object_type;
end;
' language 'plpgsql';
