Forum OpenACS Development: insert or update a row in postgres

Collapse
1: insert or update a row in postgres
Posted by Henry Minsky on 07/18/01 10:51 AM
Can someone tell me the simplest way to do the following in
Openacs-4 from a Tcl procedure (or a Postgres: procedure I guess):

If a row in a table, apm_parameter_values, exists  which has a
particular value in one of it's fields, I would like to update
the row, otherwise, I would like to insert a new row.

The way I'm doing it now is I do a db_dml update command, and
then check the result of db_resultrows; if it is 0, then I do
an insert.

Collapse
2: Response to insert or update a row in postgres (response to 1)
Posted by Don Baccus on 07/18/01 07:40 PM
There are some tricky concurrency issues here ... if two threads try to update at the same time and there are no rows, they'll both try to insert and either  one will fail (if you have a sufficiently tight unique constraint) or you'll get duplicate rows.  The first alternative's the best so double-check those key definitions on apm_parameter_values!

Such errors are unlikely in your particular scenario so you could probably just catch and report the error and ask the user to try again, or have the code try again.

You could implement a "before" insert trigger, check for the existing row, and update instead from within the trigger function.