I have spent the last couple of days reading up on SQL, of which I
know very little, and PL/pgSQl, of which I know less. I am trying
to decide how best to approach the following requirement.
Given a legal name and a common name and associated details, we wish
to insert this information into a table, entities. As well, we
believe it useful to allow a specific entity more than one common
name. So, at the moment we are considering having another table,
identifiers, that takes entity_id = entity.id (synthetic sequenced
PK for entities), the identifier_type (always 'AKNA' for this
collection of identifiers) and identifier_value =
entity.common_name.
This seems straight forward enough when initially inserting an
entity. However, it is conceivable that over the lifetime of the
system a particular entity might change its common name. For example
the former "John Tash Enterprises" might become popularly known as
"JTE Inc." while the legal name remains unchanged.
When we update the entity record and set the common_name = "JTE
Inc." then we need insert an identifier row to match. However,
identifiers for a given entity can be maintained separately from the
entity itself. It is therefore possible, indeed likely, that the
identifier "JTE Inc." for that entity already exists. Likely, but
not certain. In any case, the old identifier row remains unchanged
after the new is inserted.
The issue then is how to determine on an UPDATE entities whether it
is necessary to INSERT a new identifier using values provided from
the entities row.
>From what I have gathered, what one does is simply insert the new
identifiers row. If there is a primary key conflict then the update
fails, which the function handles gracefully. If not, then it
succeeds.
I have also formed the opinion that what one does is write a
function or functions, such as fn_aknau(entity_id, name), and tie
these with triggers to the appropriate actions on entities such as:
CREATE TRIGGER tr_entities_aioru AFTER INSERT OR UPDATE
ON entities
FOR EACH ROW
EXECUTE fn_aknai(entities.id, 'AKNA', entities.common_name);
Is my appreciation correct or am I missing the mark entirely? Is
this considered the proper place and means to accomplish this sort
of task in an RDBMS? Does it belong elsewhere? Am I correct in
inferring that the values in the columns id and common_name will be
those of entities AFTER the insert or update and that these will be
available to the body of the function? Is the trigger dependent
upon a SUCCESSFUL INSERT or UPDATE of entities or will it be called
regardless? Must the function be written in PL/pgSQl (or similar PL)
or could this function be written in straight SQL? Should it be
straight SQL if possible? What should the function return, if
anything?
Fairly basic stuff I am sure but somewhat mystifying for me at the
moment. Any help would be appreciated.
--
*** E-Mail is NOT a SECURE channel ***
James B. Byrne mailto:ByrneJB@xxxxxxxxxxxxx
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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
