I tried to checkout what tables might have metadata suitable for building the function definitions (in acs-subsite). I found a little pgplsql for getting data out of acs_function_args. But I didn't find a table by that name.
To build up the function prototypes for oracle and pg, I created two new packages for the package builder. One is pb_functions, and the other is pb_fn_attrs. Here is the datamodel for the packages:
create table pb_functions (
function_id integer not null
constraint pf_function_id_fk references acs_objects(object_id)
constraint pf_function_id_pk primary key,
oracle_pkg_id integer
constraint pf_oracle_pkg_id_fk references pb_oracle_pkg,
function_name varchar(30) not null,
owner_id integer
constraint pf_owner_id_fk references users(user_id),
return_type varchar(100) default 'integer' not null,
return_column integer
constraint pf_return_column_fk references pb_columns(column_id)
);
create table pb_fn_attrs (
fn_attr_id integer not null
constraint pfa_fn_attr_id_fk references acs_objects(object_id)
constraint pfa_fn_attr_id_pk primary key,
function_id integer not null
constraint pfa_function_id_fk references pb_functions,
column_id integer
constraint pfa_column_id_fk references pb_columns,
-- Attribute Default Value if null, just enter null,
-- otherwise quote chars, but not functions like now()
default_value varchar(4000),
-- Attribute type if not from table column
attr_type varchar(100),
-- Order of attribute in input list
attr_order integer not null,
in_out varchar(10) default 'in' not null
);
The immediate use could be in providing a place for function metadata. Second, these packages can be used to group columns from more than one table togeather. To actually write the function, I am proposing that the developer create a tcl/adp page that would take as input the function_id and produce the full package text.
Also, for current function metadata, I wonder if it might be possible to get the data out of Oracle and get it into pg. It couldn't work in all cases, but it might in most.
