--
-- acs_attribute__create_attribute/13
--
create or replace function acs_attribute__create_attribute(
character varying,
character varying,
character varying,
character varying,
character varying,
character varying,
character varying,
character varying,
integer,
integer,
integer,
character varying,
boolean
) returns int4 as $$
declare
p_object_type alias for $1;
p_attribute_name alias for $2;
p_datatype alias for $3;
p_pretty_name alias for $4;
p_pretty_plural alias for $5; -- default null
p_table_name alias for $6; -- default null
p_column_name alias for $7; -- default null
p_default_value alias for $8; -- default null
p_min_n_values alias for $9; -- default 1
p_max_n_values alias for $10; -- default 1
p_sort_order alias for $11; -- default null
p_storage alias for $12; -- default 'type_specific'
p_static_p alias for $13; -- default 'f'
begin
return acs_attribute__create_attribute(p_object_type,
p_attribute_name, p_datatype, p_pretty_name,
p_pretty_plural, p_table_name, p_column_name,
p_default_value, p_min_n_values,
p_max_n_values, p_sort_order, p_storage,
p_static_p, 'f', null, null, null, null, null, null);
end;$$ language plpgsql;
--
-- acs_attribute__create_attribute/13
--
create or replace function acs_attribute__create_attribute(
character varying,
character varying,
character varying,
character varying,
character varying,
character varying,
character varying,
integer,
integer,
integer,
integer,
character varying,
boolean
) returns int4 as $$
begin
return acs_attribute__create_attribute ($1, $2, $3, $4, $5, $6, $7, cast ($8 as varchar), $9, $10, $11, $12, $13);
end;$$ language plpgsql;
--
-- acs_attribute__create_attribute/20
--
create or replace function acs_attribute__create_attribute(
character varying,
character varying,
character varying,
character varying,
character varying,
character varying,
character varying,
character varying,
integer,
integer,
integer,
character varying,
boolean,
boolean,
character varying,
character varying,
boolean,
character varying,
character varying,
character varying
) returns int4 as $$
declare
p_object_type alias for $1;
p_attribute_name alias for $2;
p_datatype alias for $3;
p_pretty_name alias for $4;
p_pretty_plural alias for $5; -- default null
p_table_name alias for $6; -- default null
p_column_name alias for $7; -- default null
p_default_value alias for $8; -- default null
p_min_n_values alias for $9; -- default 1
p_max_n_values alias for $10; -- default 1
p_sort_order alias for $11; -- default null
p_storage alias for $12; -- default 'type_specific'
p_static_p alias for $13; -- default 'f'
p_create_column_p alias for $14; -- default 'f'
p_database_type alias for $15; -- default null
p_size alias for $16; -- default null
p_null_p alias for $17; -- default 't'
p_references alias for $18; -- default null
p_check_expr alias for $19; -- default null
p_column_spec alias for $20; -- default null
v_sort_order acs_attributes.sort_order%TYPE;
v_attribute_id acs_attributes.attribute_id%TYPE;
v_column_spec text;
v_table_name text;
v_constraint_stub text;
v_column_name text;
v_datatype record;
begin
if not exists (select 1
from acs_object_types
where object_type = p_object_type) then
raise exception 'Object type % does not exist', p_object_type;
end if;
if p_sort_order is null then
select coalesce(max(sort_order), 1) into v_sort_order
from acs_attributes
where object_type = p_object_type
and attribute_name = p_attribute_name;
else
v_sort_order := p_sort_order;
end if;
select nextval('t_acs_attribute_id_seq') into v_attribute_id;
insert into acs_attributes
(attribute_id, object_type, table_name, column_name, attribute_name,
pretty_name, pretty_plural, sort_order, datatype, default_value,
min_n_values, max_n_values, storage, static_p)
values
(v_attribute_id, p_object_type,
p_table_name, p_column_name,
p_attribute_name, p_pretty_name,
p_pretty_plural, v_sort_order,
p_datatype, p_default_value,
p_min_n_values, p_max_n_values,
p_storage, p_static_p);
if p_create_column_p then
select table_name into v_table_name from acs_object_types
where object_type = p_object_type;
if not exists (select 1
from pg_class
where relname = lower(v_table_name)) then
raise exception 'Table % for object type % does not exist', v_table_name, p_object_type;
end if;
-- Add the appropriate column to the table
-- We can only create the table column if
-- 1. the attribute is declared type_specific (generic storage uses an auxillary table)
-- 2. the attribute is not declared static
-- 3. it does not already exist in the table
if p_storage <> 'type_specific' then
raise exception 'Attribute % for object type % must be declared with type_specific storage',
p_attribute_name, p_object_type;
end if;
if p_static_p then
raise exception 'Attribute % for object type % can not be declared static',
p_attribute_name, p_object_type;
end if;
if p_table_name is not null then
raise exception 'Attribute % for object type % can not specify a table for storage', p_attribute_name, p_object_type;
end if;
if exists (select 1
from pg_class c, pg_attribute a
where c.relname::varchar = v_table_name
and c.oid = a.attrelid
and a.attname = lower(p_attribute_name)) then
raise exception 'Column % for object type % already exists',
p_attribute_name, p_object_type;
end if;
-- all conditions for creating this column have been met, now let's see if the type
-- spec is OK
if p_column_spec is not null then
if p_database_type is not null
or p_size is not null
or p_null_p is not null
or p_references is not null
or p_check_expr is not null then
raise exception 'Attribute % for object type % is being created with an explicit column_spec, but not all of the type modification fields are null',
p_attribute_name, p_object_type;
end if;
v_column_spec := p_column_spec;
else
select coalesce(p_database_type, database_type) as database_type,
coalesce(p_size, column_size) as column_size,
coalesce(p_check_expr, column_check_expr) as check_expr
into v_datatype
from acs_datatypes
where datatype = p_datatype;
v_column_spec := v_datatype.database_type;
if v_datatype.column_size is not null then
v_column_spec := v_column_spec || '(' || v_datatype.column_size || ')';
end if;
v_constraint_stub := ' constraint ' || p_object_type || '_' ||
p_attribute_name || '_';
if v_datatype.check_expr is not null then
v_column_spec := v_column_spec || v_constraint_stub || 'ck check(' ||
p_attribute_name || v_datatype.check_expr || ')';
end if;
if not p_null_p then
v_column_spec := v_column_spec || v_constraint_stub || 'nn not null';
end if;
if p_references is not null then
v_column_spec := v_column_spec || v_constraint_stub || 'fk references ' ||
p_references || ' on delete';
if p_null_p then
v_column_spec := v_column_spec || ' set null';
else
v_column_spec := v_column_spec || ' cascade';
end if;
end if;
end if;
execute 'alter table ' || v_table_name || ' add ' || p_attribute_name || ' ' ||
v_column_spec;
end if;
return v_attribute_id;
end;$$ language plpgsql;
OpenACS – The Toolkit for Online Communities : ACS API Browser : All PL/SQL Subprograms : One PL/SQL Subprogram
Begin main content
