[database] Merge queries

[David Timothy Strauss]

"Since the return value from db_update() is the number of rows *affected*, not
*changed*, which means our usual update-or-insert routine doesn't work."

I assume you mean it returns the number of rows *changed* rather than *matching* rows.

The way I solve the issue is by doing the operations:
(1) UPDATE
(2) If updated changed zero rows, do a SELECT on the criteria
(3) If the SELECT returned zero rows, do an INSERT

This would all be in a transaction.

----- "Larry Garfield" <larry at garfieldtech.com> wrote:

> So much progress has been made, now that I have a working Postgres
> install 
> thanks to James Walker and Sammy Spets. :-)
> 
> The next item on the list is Merge queries.  
> 
> http://en.wikibooks.org/wiki/SQL_dialects_reference/Write_queries/Replace_query
> 
> Basically nothing supports the SQL:2003 merge query AFAIK.  
> 
> In MySQL, we're using ON DUPLICATE KEY UPDATE, which is working like a
> champ.
> 
> For Postgres, the original concept was to do:
> 
> // start transaction.
> try {
>   db_insert();
> }
> catch (Exception $e) {
>   db_update();
> }
> // end transaction
> 
> Since the return value from db_update() is the number of rows
> *affected*, not 
> *changed*, which means our usual update-or-insert routine doesn't
> work.
> 
> However, Sammy pointed out that logic will not work, period.  If the
> INSERT 
> statement throws an exception, it's because the database itself is now
> in an 
> invalid state and will not accept anything other than a rollback. 
> Even if we 
> didn't have the transaction wrapping there, we couldn't have any
> transactions 
> active at all for a merge query with this logic.  So this is not an
> option.
> 
> So we need a way to handle merge queries in Postgres.  Josh, this is
> where you 
> come in. :-)  How can we do an atomic, non-exception-using merge query
> or 
> reasonable emulation?  We've just kicked Postgres 7.4 to the curb, so
> 
> whatever Postgres 8.1 can do is available to us.
> 
> -- 
> Larry Garfield
> larry at garfieldtech.com
> _______________________________________________
> Database mailing list
> Database at drupal.org
> http://mail.drupal.org/mailman/listinfo/database