Re: SQL design pattern for a delta trigger?

On Dec 7, 2007, at 6:29 AM, Ted Byers wrote:

>
> --- Erik Jones <erik@myemma.com> wrote:
>
>>
>> On Dec 6, 2007, at 2:36 PM, Ted Byers wrote:
>>
>> [snip]
>> What you want to do here for handling the update v.
>> insert is called
>> an "UPSERT".  Basically, what you do is run the
>> update as if the row
>> exists and catch the exception that is thrown if it
>> doesn't at which
>> point you insert the record with the end date =
>> now().  After that
>> you can proceed normally with creating the new
>> record with start date
>> = now() and end date = NULL.
>>
> Thanks Eric.  Do you know of an URL where this is
> discussed or where I can find an example.  None of my
> books discuss this, and my search using google has so
> far produced only noise.

http://www.postgresql.org/docs/current/static/plpgsql-control-
structures.html#PLPGSQL-UPSERT-EXAMPLE might be a good place to start.

Cheers,
   Steve