pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRules Question
3+ messages / 3 participants
[nested] [flat]
* Rules Question
@ 1999-08-30 11:20 Charles Tassell <ctassell@isn.net>
0 siblings, 0 replies; 3+ messages in thread
From: Charles Tassell @ 1999-08-30 11:20 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 3+ messages in thread
* rules question
@ 2003-04-30 04:00 ed despard <despareg@clarkson.edu>
2003-04-30 09:26 ` Re: rules question Richard Huxton <dev@archonet.com>
0 siblings, 1 reply; 3+ messages in thread
From: ed despard @ 2003-04-30 04:00 UTC (permalink / raw)
To: pgsql-general@postgresql.org; +Cc: pgsql-sql
hi, i have a table named pursuit which has no data in it, currently to
get data for the table i have a function that i call like: select *
from pursuit_select_function(raceid), but i put in the raceid that im
selecting for. i would like to be able to treat this table like a view
and make a query like select * from pursuit where raceid=9 or somthing
and have a rule rewrite this into the function call. is there a way to
have a rule that rewrites the query based on the qualifier of the query?
the reason im not just using a normal view is that i have a sequence
that needs to be reset before each select, so the
pursuit_select_function resets the sequence and does the select query.
thanks.
ed
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: rules question
2003-04-30 04:00 rules question ed despard <despareg@clarkson.edu>
@ 2003-04-30 09:26 ` Richard Huxton <dev@archonet.com>
0 siblings, 0 replies; 3+ messages in thread
From: Richard Huxton @ 2003-04-30 09:26 UTC (permalink / raw)
To: ed despard <despareg@clarkson.edu>; pgsql-general@postgresql.org
On Wednesday 30 Apr 2003 5:00 am, ed despard wrote:
> hi, i have a table named pursuit which has no data in it, currently to
> get data for the table i have a function that i call like: select *
> from pursuit_select_function(raceid), but i put in the raceid that im
> selecting for. i would like to be able to treat this table like a view
> and make a query like select * from pursuit where raceid=9 or somthing
> and have a rule rewrite this into the function call. is there a way to
> have a rule that rewrites the query based on the qualifier of the query?
I think you're out of luck here.
For "where raceid=9" it's easy enough - just call "function(9)".
What if someone uses "where raceid in (7,8,9)" - does the planner write that
as "function(7) or function (8) or function(9)" and how does that interact
with your side-effects?
> the reason im not just using a normal view is that i have a sequence
> that needs to be reset before each select, so the
> pursuit_select_function resets the sequence and does the select query.
I was thinking you could create a rule with multiple queries for the action,
but unfortunately that's not supported for ON SELECT rules (according to my
docs). If it was, you could reset the sequence as the first query, then do
your processing for the second.
I take it you're using the sequence to number output rows?
--
Richard Huxton
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2003-04-30 09:26 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-08-30 11:20 Rules Question Charles Tassell <ctassell@isn.net>
2003-04-30 04:00 rules question ed despard <despareg@clarkson.edu>
2003-04-30 09:26 ` Re: rules question Richard Huxton <dev@archonet.com>
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