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

[Andrew McMillan]

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.

-------------------------------------------------------------------------
Andrew @ Catalyst .Net .NZ  Ltd,  PO Box 11-053, Manners St,  Wellington
WEB: http://catalyst.net.nz/            PHYS: Level 2, 150-154 Willis St
DDI: +64(4)803-2201      MOB: +64(272)DEBIAN      OFFICE: +64(4)499-2267
                     Planning an election?  Call us!
-------------------------------------------------------------------------

-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: application/pgp-signature
Size: 189 bytes
Desc: This is a digitally signed message part
Url : http://mail.pm.org/pipermail/wellington-pm/attachments/20061224/0c2f308d/attachment.bin