tww
.tww_app
Tables
(current)
Columns
Constraints
Relationships
Orphan Tables
Anomalies
Routines
generate_oid(schema_name text, table_name text)
Parameters
Name
Type
Mode
schema_name
text
IN
table_name
text
IN
Definition
DECLARE myrec_prefix record; myrec_shortcut record; myrec_seq record; BEGIN -- first we have to get the OID prefix BEGIN SELECT prefix::text INTO myrec_prefix FROM tww_sys.oid_prefixes WHERE active = TRUE; EXCEPTION WHEN NO_DATA_FOUND THEN RAISE EXCEPTION 'no active record found in table tww_sys.oid_prefixes'; WHEN TOO_MANY_ROWS THEN RAISE EXCEPTION 'more than one active records found in table tww_sys.oid_prefixes'; END; -- test if prefix is of correct length IF char_length(myrec_prefix.prefix) != 8 THEN RAISE EXCEPTION 'character length of prefix must be 8'; END IF; --get table 2char shortcut BEGIN SELECT shortcut_en INTO STRICT myrec_shortcut FROM tww_sys.dictionary_od_table WHERE tablename = table_name; EXCEPTION WHEN NO_DATA_FOUND THEN RAISE EXCEPTION 'dictionary entry for table % not found', table_name; WHEN TOO_MANY_ROWS THEN RAISE EXCEPTION 'dictonary entry for table % not unique', table_name; END; --get sequence for table EXECUTE format('SELECT nextval(''%1$I.seq_%2$I_oid'') AS seqval', schema_name, table_name) INTO myrec_seq; IF NOT FOUND THEN RAISE EXCEPTION 'sequence for table % not found', table_name; END IF; RETURN myrec_prefix.prefix || myrec_shortcut.shortcut_en || to_char(myrec_seq.seqval,'FM000000'); END;