postgresql7.1
select upper(coalesce(attr.table_name,t.table_name)) as attr_table_name,
upper(coalesce(attr.column_name, attr.attribute_name)) as attr_column_name,
attr.ancestor_type, attr.min_n_values, attr.default_value
from acs_object_type_attributes attr,
(select t2.object_type, t2.table_name, (tree_level(t1.tree_sortkey) - tree_level(t2.tree_sortkey)) + 1 as type_level
from acs_object_types t1, acs_object_types t2
where t1.tree_sortkey between t2.tree_sortkey and tree_right(t2.tree_sortkey)
and t1.object_type = :object_type) t
where attr.ancestor_type = t.object_type
and attr.object_type = :object_type
order by t.type_level
select t2.object_type
from acs_object_types t1, acs_object_types t2
where t2.dynamic_p = 't'
and t2.tree_sortkey between t1.tree_sortkey and tree_right(t1.tree_sortkey)
and t1.object_type = :object_type
-- select case when exists (select 1
-- from user_objects
-- where status = 'INVALID'
-- and object_name = upper(:package_name)
-- and object_type = upper(:type))
-- then 0 else 1 end
select 1 from dual;
select t2.object_type as ancestor_type
from acs_object_types t1, acs_object_types t2
where t1.tree_sortkey between t2.tree_sortkey and tree_right(t2.tree_sortkey)
and t1.object_type = :object_type
select t2.object_type as sub_type
from acs_object_types t1, acs_object_types t2
where t2.tree_sortkey between t1.tree_sortkey and tree_right(t1.tree_sortkey)
and t1.object_type = :object_type
select a.attribute_id,
coalesce(a.table_name, t.table_name) as table_name,
coalesce(a.column_name, a.attribute_name) as attribute_name,
a.pretty_name,
a.datatype,
case when a.min_n_values = 0 then 'f' else 't' end as required_p,
a.default_value,
t.table_name as object_type_table_name,
t.id_column as object_type_id_column
from acs_object_type_attributes a,
(select t.object_type, t.table_name, t.id_column, tree_level(t.tree_sortkey) as type_level
from acs_object_types t, acs_object_types t2
where t.tree_sortkey between t2.tree_sortkey and tree_right(t2.tree_sortkey)
and t2.object_type = :start_with) t
where a.object_type = :object_type
and t.object_type = a.ancestor_type $storage_clause
order by type_level, sort_order, attribute_id
select cols.table_name, cols.column_name
from user_tab_columns cols,
(select upper(t2.table_name) as table_name
from acs_object_types t1, acs_object_types t2
where t1.tree_sortkey between t2.tree_sortkey and tree_right(t2.tree_sortkey)
and t1.object_type = :object_type) t
where cols.column_name in
(select args.arg_name
from acs_function_args args
where args.function = upper(:package_name) || '__' || upper(:object_name))
and cols.table_name = t.table_name
select args.arg_name
from acs_function_args args
where args.function = upper(:package_name) || '__' || upper(:object_name)
select 1
select ${package_name}__new([plpgsql_utility::generate_attribute_parameter_call \
-prepend ":" \
${package_name}__new \
$pieces])
select args.arg_name
from acs_function_args args
where args.function = upper(:supertype_package_name) || '__NEW'
begin
perform drop_package('${package_name}');
perform define_function_args('${package_name}__new','[plpgsql_utility::define_function_args $attribute_list]');
CREATE FUNCTION ${package_name}__new([plpgsql_utility::generate_function_signature $attribute_list])
RETURNS [plpgsql_utility::table_column_type ${table_name} ${id_column}] AS $$
DECLARE
[plpgsql_utility::generate_attribute_parameters $attribute_list];
v_$id_column ${table_name}.${id_column}%TYPE;
BEGIN
v_$id_column := ${supertype_package_name}__new (
[plpgsql_utility::generate_attribute_parameter_call_from_attributes \
-prepend "p_" \
"${supertype_package_name}__new" \
$supertype_attr_list]
);
insert into ${table_name}
($id_column[plsql_utility::generate_attribute_dml -ignore [list $id_column] $table_name $attribute_list])
values
(v_$id_column[plsql_utility::generate_attribute_dml -prepend "p_" -ignore [list $id_column] $table_name $attribute_list]);
return v_$id_column;
END;
$$ LANGUAGE plpgsql;
CREATE FUNCTION ${package_name}__delete (
p_${id_column} [plpgsql_utility::table_column_type ${table_name} ${id_column}]
) RETURNS integer AS $$
DECLARE
BEGIN
perform ${supertype_package_name}__delete( p_${id_column} );
return 1;
END;
$$ LANGUAGE plpgsql;
return null;
end;
select 1;
select 1 from dual
select ${__package_name}__${__object_name}([plpgsql_utility::generate_attribute_parameter_call \
-prepend ":" \
${__package_name}__${__object_name} \
$pieces])