CREATE RULE with WHERE clause

pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed

CREATE RULE with WHERE clause
3+ messages / 3 participants
[nested] [flat]

* CREATE RULE with WHERE clause
@ 2007-06-05 13:11  Ranieri Mazili <ranieri.oliveira@terra.com.br>
  0 siblings, 2 replies; 3+ messages in thread

From: Ranieri Mazili @ 2007-06-05 13:11 UTC (permalink / raw)
  To: pgsql-general@postgresql.org; pgsql-sql

Hello,

I need to create a rule, but I need that it have a WHERE clause, how bellow:


CREATE RULE rule_role_sul AS
    ON SELECT TO t1 WHERE roles = 'role_sul'
    DO INSTEAD 
        SELECT field1, field2 FROM t2;

CREATE RULE rule_role_sul AS
    ON SELECT TO t1 WHERE roles = 'role_norte'
    DO INSTEAD 
        SELECT field3, field4 FROM t2;

Someone knows how can I do it?

I appreciate any help

Thanks 




^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: CREATE RULE with WHERE clause
@ 2007-06-05 14:07  Richard Broersma Jr <rabroersma@yahoo.com>
  parent: Ranieri Mazili <ranieri.oliveira@terra.com.br>
  1 sibling, 0 replies; 3+ messages in thread

From: Richard Broersma Jr @ 2007-06-05 14:07 UTC (permalink / raw)
  To: Ranieri Mazili <ranieri.oliveira@terra.com.br>; pgsql-general@postgresql.org; pgsql-sql

> CREATE RULE rule_role_sul AS
>     ON SELECT TO t1 WHERE roles = 'role_sul'
>     DO INSTEAD 
>         SELECT field1, field2 FROM t2;
> 
> CREATE RULE rule_role_sul AS
>     ON SELECT TO t1 WHERE roles = 'role_norte'
>     DO INSTEAD 
>         SELECT field3, field4 FROM t2;

From: http://www.postgresql.org/docs/8.2/interactive/sql-createrule.html
...
WHERE condition
Any SQL conditional expression (returning boolean). The condition expression may not refer to any
tables except NEW and OLD, and may not contain aggregate functions. 
...

This statement to me implies that only ON {INSERT | UPDATE | DELETE } actions can use the WHERE
syntax since only inserts, updates, and deletes product the NEW.* and OLD.* tables.  

Also, NEW.* and OLD.* only apply to a single tuple in the view that is being changed.  So I do not
think you can not use the where syntax in your query since it does not and cannot reference a NEW
or OLD tuple.

Regards,
Richard Broersma Jr.

^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: CREATE RULE with WHERE clause
@ 2007-06-05 14:40  Michael Glaesemann <grzm@seespotcode.net>
  parent: Ranieri Mazili <ranieri.oliveira@terra.com.br>
  1 sibling, 0 replies; 3+ messages in thread

From: Michael Glaesemann @ 2007-06-05 14:40 UTC (permalink / raw)
  To: Ranieri Mazili <ranieri.oliveira@terra.com.br>; +Cc: pgsql-general@postgresql.org; pgsql-sql


On Jun 5, 2007, at 8:11 , Ranieri Mazili wrote:

> CREATE RULE rule_role_sul AS
>    ON SELECT TO t1 WHERE roles = 'role_sul'
>    DO INSTEAD        SELECT field1, field2 FROM t2;
>
> CREATE RULE rule_role_sul AS
>    ON SELECT TO t1 WHERE roles = 'role_norte'
>    DO INSTEAD        SELECT field3, field4 FROM t2;

I don't believe you can include a WHERE clause like this. From the  
documentation[1]:

http://www.postgresql.org/docs/8.2/interactive/rules-views.html#RULES- 
SELECT

> Currently, there can be only one action in an ON SELECT rule, and  
> it must be an unconditional SELECT action that is INSTEAD. This  
> restriction was required to make rules safe enough to open them for  
> ordinary users, and it restricts ON SELECT rules to act like views.

You can use views instead (which are implemented using the rule  
system), but I'm not sure how you would handle it in this case. I  
believe you'd have to implement two views:

CREATE VIEW rule_role_sul AS
SELECT field1, field2
FROM t2
WHERE roles = 'role_sul';

CREATE VIEW rule_role_norte AS
SELECT field3, field4
FROM t2
WHERE roles = 'role_norte';

Hope this helps.

Michael Glaesemann
grzm seespotcode net





^ permalink  raw  reply  [nested|flat] 3+ messages in thread

end of thread, other threads:[~2007-06-05 14:40 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-06-05 13:11 CREATE RULE with WHERE clause Ranieri Mazili <ranieri.oliveira@terra.com.br>
2007-06-05 14:07 ` Richard Broersma Jr <rabroersma@yahoo.com>
2007-06-05 14:40 ` Michael Glaesemann <grzm@seespotcode.net>

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