[Wellington-pm] Perl database stuff....

[Cliff Pratt]

Andrew McMillan wrote:
> On Sun, 2006-12-24 at 14:29 +1300, Cliff Pratt wrote:
>> I want to write a row, using Perl, to a PostgreSQL database, *but only 
>> if the row does not already exist*. If I add a row, I need to know the 
>> ID of the row.
>>
>> Currently what I have is:
>>
>> 1) Select the row using a WHERE clause to select the row I want to add, 
>> to see if it is already there.
>>
>> 2) If the row does exist return an error (details don't matter).
>>
>> 3) If the row doesn't exist insert it, defaulting the ID which is 
>> generated by a sequence.
>>
>> 4) Read it again to find the ID which was generated during the insert.
>>
>> Blah! This works but is long-winded, untidy and generally blah!
>>
>> Is there a smarter way to do it, either on the Perl side or on the 
>> database side?
> 
> Perhaps.  Certainly using nextval() or currval() are better approaches
> for getting hold of the generated sequence.
> 
> Here are some more alternative approaches:
> 
> (a) On the database side you could write a function which accepted all
> of the row parameters, selected the row returning the id (possibly
> throwing an error).  If the row didn't exist it could insert the row,
> returning the id either using currval('sequence') to get the sequence
> value after the fact, or by using nextval('sequence') and explicitly
> assigning that for the ID column.
> 
> (b) On the database side you could write a fancy insert ... select ...
> statement such as:
> 
>   INSERT INTO table1 (col1,col2)
>     SELECT 1 AS col1, 2 AS col2 FROM table1
>        WHERE NOT exists(SELECT 1 FROM table1 WHERE col1=1 AND col2=2)
>        LIMIT 1;
> 
> That's a pretty sneaky one, and might not make maintenance of the
> program as straightforward as it might otherwise be.  It won't give you
> an error when the row already exists - it will just silently not insert
> a row, which can sometimes be what you want.
> 
> 
> (c) In the perl you can make it "select, select, insert" as:
>  1. select the record (and fail).
>  2. select the sequence nextval
>  3. insert the row using the sequence.
> 
> Which is actually the way I would tend to do it myself because it seems
> so much cleaner to know the ID in advance and then write multiple SQL
> calls using that value.  I tend to take the (a) approach when I have
> some values that I want to 'either update or insert these values', which
> is slightly different from your question here.
> 
> Cheers,
> 					Andrew.
 >
Thanks for that. I like solution c) which is a bit neater than my 
solution. Re solution b), I was wondering if there was a DB side answer, 
but that is too tricky for me! a) is interesting and I might look 
further into that.

Cheers,

Cliff