-- -- acs_object__get_attribute_storage/2 -- create or replace function acs_object__get_attribute_storage( integer, character varying ) returns text as $$ declare object_id_in alias for $1; attribute_name_in alias for $2; -- these three are the out variables v_column varchar; v_table_name varchar; v_key_sql text; v_object_type acs_attributes.object_type%TYPE; v_static acs_attributes.static_p%TYPE; v_attr_id acs_attributes.attribute_id%TYPE; v_storage acs_attributes.storage%TYPE; v_attr_name acs_attributes.attribute_name%TYPE; v_id_column varchar(200); v_sql text; v_return text; v_rec record; begin -- select -- object_type, id_column -- from -- acs_object_types -- connect by -- object_type = prior supertype -- start with -- object_type = (select object_type from acs_objects -- where object_id = object_id_in) -- Determine the attribute parameters select a.attribute_id, a.static_p, a.storage, a.table_name, a.attribute_name, a.object_type, a.column_name, t.id_column into v_attr_id, v_static, v_storage, v_table_name, v_attr_name, v_object_type, v_column, v_id_column from acs_attributes a, (select o2.object_type, o2.id_column from acs_object_types o1, acs_object_types o2 where o1.object_type = (select object_type from acs_objects o where o.object_id = object_id_in) and o1.tree_sortkey between o2.tree_sortkey and tree_right(o2.tree_sortkey) ) t where a.attribute_name = attribute_name_in and a.object_type = t.object_type; if NOT FOUND then raise EXCEPTION '-20000: No such attribute % for object % in acs_object.get_attribute_storage.', attribute_name_in, object_id_in; end if; -- This should really be done in a trigger on acs_attributes, -- instead of generating it each time in this function -- If there is no specific table name for this attribute, -- figure it out based on the object type if v_table_name is null or v_table_name = '' then -- Determine the appropriate table name if v_storage = 'generic' then -- Generic attribute: table name/column are hardcoded v_column := 'attr_value'; if v_static = 'f' then v_table_name := 'acs_attribute_values'; v_key_sql := '(object_id = ' || object_id_in || ' and ' || 'attribute_id = ' || v_attr_id || ')'; else v_table_name := 'acs_static_attr_values'; v_key_sql := '(object_type = ''' || v_object_type || ''' and ' || 'attribute_id = ' || v_attr_id || ')'; end if; else -- Specific attribute: table name/column need to be retreived if v_static = 'f' then select table_name, id_column into v_table_name, v_id_column from acs_object_types where object_type = v_object_type; if NOT FOUND then raise EXCEPTION '-20000: No data found for attribute %::% object_id % in acs_object.get_attribute_storage', v_object_type, attribute_name_in, object_id_in; end if; else raise EXCEPTION '-20000: No table name specified for storage specific static attribute %::% object_id % in acs_object.get_attribute_storage.',v_object_type, attribute_name_in, object_id_in; end if; end if; else -- There is a custom table name for this attribute. -- Get the id column out of the acs_object_tables -- Raise an error if not found select id_column into v_id_column from acs_object_type_tables where object_type = v_object_type and table_name = v_table_name; if NOT FOUND then raise EXCEPTION '-20000: No data found for attribute %::% object_id % in acs_object.get_attribute_storage', v_object_type, attribute_name_in, object_id_in; end if; end if; if v_column is null or v_column = '' then if v_storage = 'generic' then v_column := 'attr_value'; else v_column := v_attr_name; end if; end if; if v_key_sql is null or v_key_sql = '' then if v_static = 'f' then v_key_sql := v_id_column || ' = ' || object_id_in ; else v_key_sql := v_id_column || ' = ''' || v_object_type || ''''; end if; end if; return v_column || ',' || v_table_name || ',' || v_key_sql; end;$$ language plpgsql;