Re: insert/update

Paul Thomas wrote:
> On 26/05/2004 11:54 Tom Allison wrote:

>> What I'm trying to do is create a counter for each key, insert a value
>> of 1 or increment the value by 1 and then set another specific row
>> (where key = $key) to always increment by 1.

> Use a sequence.

Not sure it's going to help him here. Looks like a specific count is needed.

Tom - you don't say precisely what you're trying to do, but I like to
keep my code simple by making sure there is always a row available.

Example (a poor one, perhaps):
cart_details (cart_id, owner, ...)
cart_summary (cart_id, num_items, tot_value)
cart_items (cart_id, item_id, quantity)

Create a trigger on cart_details that after inserting a new row, inserts
zeroed totals into cart_summary. That way when you add new items to the
cart, you know there is always a total to update.

On the other hand, you might need cart_summary to be something like:
cart_summary (cart_id, item_category, num_items, tot_value)
In this case you either create zeroed totals for every value of
"item_category" or you need a trigger on cart_items rather than
cart_details. If the trigger is on cart_items and you can have more than
one user adding items to the cart at the same time, then you'll need to
think about concurrency issues and locking.

Useful sections of the manual are "Procedural Languages:pl/pgsql" and
"SQL command reference". You can probably find example triggers via the
techdocs site.

--
Richard Huxton
Archonet Ltd