On Mon, April 6, 2009 17:15, Dann Corbit wrote: > > The pedagogic solution for this type of problem is called merge. > The last I knew, PostgreSQL did not directly support merge. > So you can accomplish the same thing in two stages: > 1. Check for existence and perform an update if the key is present > 2. If the key is not present, then perform an insert. > > Again, this may or may not be the right thing to do. > Forgive my obtuseness, but what does the preliminary SELECT accomplish? When the trigger fires we already know whether or not the entities row existed previously, what we are deciding is how to handle the concurrent identifiers table entry. I initially thought along these lines (select insert/update depending on the return value) but I gradually realized that it did not matter whether the identifier row was already there or not. If it exists then an UNIQUE key constraint prevents duplicates. If it does not exist then the INSERT succeeds. The previous identifier associated with the original common name has to remain on file to allow lookups by former names. Thus, we never update an identifier row in this fashion. -- *** 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
