From: Michael Glaesemann <grzm@seespotcode.net>
To: Ranieri Mazili <ranieri.oliveira@terra.com.br>
Cc: pgsql-general@postgresql.org
Cc: pgsql-sql@postgresql.org
Subject: Re: CREATE RULE with WHERE clause
Date: Tue, 5 Jun 2007 09:40:50 -0500
Message-ID: <41FB1D71-534B-4358-9B09-A60C35F948B9@seespotcode.net> (raw)
In-Reply-To: <4665611F.9020500@terra.com.br>
References: <4665611F.9020500@terra.com.br>
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
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: grzm@seespotcode.net, ranieri.oliveira@terra.com.br, pgsql-general@postgresql.org
Subject: Re: CREATE RULE with WHERE clause
In-Reply-To: <41FB1D71-534B-4358-9B09-A60C35F948B9@seespotcode.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