Skip site navigation (1) Skip section navigation (2)

Re: RULES doesn't work as expected

From: Richard Huxton <dev(at)archonet(dot)com>
To: John Hansen <john(at)geeknet(dot)com(dot)au>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: RULES doesn't work as expected
Date: 2005-01-12 13:55:17
Message-ID: 41E52C45.6030102@archonet.com (view raw or flat)
Thread:
Lists: pgsql-bugs
John Hansen wrote:
> Right, except:
> 
> create table test (a text, b int); create or replace rule test_rule
> as on insert to test where exists(select 1 from test where a = NEW.a)
> do instead select * from test;
> 
> insert into test (a,b) VALUES ('first',2); a   | b -------+--- first
> | 2 (1 row)
> 
> select * from test; a   | b -------+--- first | 2 (1 row)
> 
> Now, the select on the first insert should NOT have happened.....
> Since this is a do instead rule. The insert should of course happen,
> since it's not present in the table.
> 
> Or am I missing the point completely?

You are, but it's not your fault. You're still thinking of it as a 
sequence of instructions, try thinking of it as a single expression that 
gets evaluated.

To quote from the (v8.0) docs (ch 32 - The Rule System), for your case:
"Qualification given and INSTEAD
     the query tree from the rule action with the rule qualification and 
the original query tree's qualification; and the original query tree 
with the negated rule qualification added"

So, in your case you get two branches:
1. INSERT ... WHERE NOT EXISTS (...)
2. SELECT * FROM TEST WHERE EXISTS (...)

Is this making sense?
--
   Richard Huxton
   Archonet Ltd

In response to

pgsql-bugs by date

Next:From: Sokolov YuraDate: 2005-01-12 14:47:51
Subject: BUG #1391: Perl trusted language triggers can't properly access $_SHARED
Previous:From: Pailloncy Jean-GerardDate: 2005-01-12 13:34:21
Subject: 8rc5 on OpenBSD

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group