This is what I have come up with. Comments are welcomed.
CREATE OR REPLACE FUNCTION hll_pg_fn_ident_insert()
RETURNS TRIGGER AS $pg_fn$
-- ROW AFTER TRIGGER
-- trigger passes identifier_type, _value and _description
-- received as ARGV[0], ARGV[1] and ARGV[2]
BEGIN
INSERT INTO identifiers(
entity_id,
identifier_type,
identifier_value,
identifier_description)
VALUES(
NEW.id,
TG_ARGV[0],
TG.ARGV[1],
TG_ARGV[2]);
-- Assume the INSERT fails because of a unique key violation,
-- (entity_id + identifier_type + identifier_value)
--
-- This does not matter since we only need ensure that this
-- alias exists, so handle the exception and return:
EXCEPTION
WHEN unique_violation THEN
-- do nothing
NULL;
END;
$pg_fn$ LANGUAGE plpgsql;
COMMENT ON FUNCTION hll_pg_fn_ident_insert IS
'Used by entities trigger. Inserts a corresponding
identifiers row.'
CREATE TRIGGER hll_pg_tr_entity_identifier_akna
AFTER INSERT OR UPDATE ON entities
FOR EACH ROW EXECUTE PROCEDURE hll_pg_fn_identifier_insert(
"AKNA", entities.entity_common_name, "Common Name
auto-insert");
COMMENT ON TRIGGER hll_pg_tr_entity_identifier_akna IS
'Inserts an alias identifier for common name if one does not
exist'
--
*** E-Mail is NOT a SECURE channel ***
James B. Byrne mailto:byrn...@harte-lyne.ca
Harte & Lyne Limited http://www.harte-lyne.ca
9 Brockley Drive vox: +1 905 561 1241
Hamilton, Ontario fax: +1 905 561 0757
Canada L8E 3C3
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
