From: Charles Tassell <ctassell@isn.net>
To: pgsql-sql@postgreSQL.org
Subject: Rules Question
Date: Mon, 30 Aug 1999 08:20:37 -0300
Message-ID: <4.1.19990830074918.00a0f100@mailer.isn.net> (raw)
I have a question on using INSERT rules. What I am attempting to do is to
create a rule that checks to see if the item being inserted already exists.
If it does exist, increase the stored quantity bye the NEW.quantity,
otherwise, just handle the INSERT as normal.
Here is what I've tried
CREATE TABLE orders (
cust_id int,
item_id text,
quantity int
);
CREATE RULE combine_dupes AS ON INSERT TO orders
WHERE NEW.item_id IN
(SELECT item_id FROM orders
WHERE cust_id = NEW.cust_id AND item_id = NEW.item_id)
DO
UPDATE orders SET quantity = NEW.quantity + quantity
WHERE cart_id = NEW.cart_id AND item_ID = NEW.item_id;
I've also tried it as a DO INSTEAD and without the "AND item_id =
NEW.item_id" clause (which shouldn't be needed) but they don't work either.
So, what's the deal? Am I making a syntax error, or do I need to use a
function or trigger in order to do what I'm trying?
BTW: Please respond or cc directly to me, I only subscribe to
pgsql-general, I just thought this was more SQL related.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: ctassell@isn.net
Subject: Re: Rules Question
In-Reply-To: <4.1.19990830074918.00a0f100@mailer.isn.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox