agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedDrop or disable or bypass "_return" rule on select on a view.
8+ messages / 6 participants
[nested] [flat]
* Drop or disable or bypass "_return" rule on select on a view.
@ 2015-05-28 06:23 Shashwat Arghode <shashwatarghode@gmail.com>
2015-05-28 09:19 ` Re: Drop or disable or bypass "_return" rule on select on a view. Vincenzo Campanella <vinz65@gmail.com>
2015-05-28 10:34 ` Re: Drop or disable or bypass "_return" rule on select on a view. Luca Ferrari <fluca1978@infinito.it>
2015-05-28 13:53 ` Re: Drop or disable or bypass "_return" rule on select on a view. Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 3 replies; 8+ messages in thread
From: Shashwat Arghode @ 2015-05-28 06:23 UTC (permalink / raw)
To: pgsql-novice@postgresql.org; pgsql-sql
Hi all,
I am using postgres 9.3.4 and have an on_select rule "_return" on a view.
I want to drop or disable or bypass that rule.
Is there any way it can be done without dropping the view??
Because when i try to drop it, it returns
ERROR: cannot drop rule _RETURN on view viewname because view viewname
requires it
HINT: You can drop viewname instead.
CONTEXT: SQL statement "drop rule "_RETURN" on viewname"
PL/pgSQL function inline_code_block line 12 at EXECUTE statement
and i don't want to drop the view.
when i try to disable it using :
alter table viewname disable rule _return
it returns
ERROR: "viewname" is not a table
Bypassing rule for a single query or disabling it for some time and then
enable it will also work for me.
Can it be done ?
Thanks,
Shashwat.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Drop or disable or bypass "_return" rule on select on a view.
2015-05-28 06:23 Drop or disable or bypass "_return" rule on select on a view. Shashwat Arghode <shashwatarghode@gmail.com>
@ 2015-05-28 09:19 ` Vincenzo Campanella <vinz65@gmail.com>
2015-05-28 09:44 ` Re: Drop or disable or bypass "_return" rule on select on a view. Shashwat Arghode <shashwatarghode@gmail.com>
2 siblings, 1 reply; 8+ messages in thread
From: Vincenzo Campanella @ 2015-05-28 09:19 UTC (permalink / raw)
To: Shashwat Arghode <shashwatarghode@gmail.com>; pgsql-novice@postgresql.org; pgsql-sql
Il 28.05.2015 08:23, Shashwat Arghode ha scritto:
> Hi all,
> I am using postgres 9.3.4 and have an on_select rule "_return" on a view.
> I want to drop or disable or bypass that rule.
> Is there any way it can be done without dropping the view??
>
> Because when i try to drop it, it returns
>
> ERROR: cannot drop rule _RETURN on view viewname because view
> viewname requires it
> HINT: You can drop viewname instead.
> CONTEXT: SQL statement "drop rule "_RETURN" on viewname"
> PL/pgSQL function inline_code_block line 12 at EXECUTE statement
>
> and i don't want to drop the view.
>
> when i try to disable it using :
> alter table viewname disable rule _return
> it returns
> ERROR: "viewname" is not a table
>
>
> Bypassing rule for a single query or disabling it for some time and
> then enable it will also work for me.
>
> Can it be done ?
>
> Thanks,
> Shashwat.
Hi Shashwat
I am no PG guru but I guess that, since "viewname" is a view and not a
table, you should use "alter view" and not "alter table". See:
http://www.postgresql.org/docs/9.3/static/sql-alterview.html
--
Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Drop or disable or bypass "_return" rule on select on a view.
2015-05-28 06:23 Drop or disable or bypass "_return" rule on select on a view. Shashwat Arghode <shashwatarghode@gmail.com>
2015-05-28 09:19 ` Re: Drop or disable or bypass "_return" rule on select on a view. Vincenzo Campanella <vinz65@gmail.com>
@ 2015-05-28 09:44 ` Shashwat Arghode <shashwatarghode@gmail.com>
2015-05-28 19:34 ` Re: [NOVICE] Drop or disable or bypass "_return" rule on select on a view. Faisal Karim <faisalk@furniture-pro.com>
0 siblings, 1 reply; 8+ messages in thread
From: Shashwat Arghode @ 2015-05-28 09:44 UTC (permalink / raw)
To: Vincenzo Campanella <vinz65@gmail.com>; +Cc: pgsql-novice@postgresql.org; pgsql-sql
Hi Vincenzo,
There is no option like disable rule in alter view documentation.
Still after trying it, it gives same error as follows.
ERROR: "viewname" is not a table
Thanks,
Shashwat.
On Thu, May 28, 2015 at 2:49 PM, Vincenzo Campanella <vinz65@gmail.com>
wrote:
> Il 28.05.2015 08:23, Shashwat Arghode ha scritto:
>
>> Hi all,
>> I am using postgres 9.3.4 and have an on_select rule "_return" on a view.
>> I want to drop or disable or bypass that rule.
>> Is there any way it can be done without dropping the view??
>>
>> Because when i try to drop it, it returns
>>
>> ERROR: cannot drop rule _RETURN on view viewname because view viewname
>> requires it
>> HINT: You can drop viewname instead.
>> CONTEXT: SQL statement "drop rule "_RETURN" on viewname"
>> PL/pgSQL function inline_code_block line 12 at EXECUTE statement
>>
>> and i don't want to drop the view.
>>
>> when i try to disable it using :
>> alter table viewname disable rule _return
>> it returns
>> ERROR: "viewname" is not a table
>>
>>
>> Bypassing rule for a single query or disabling it for some time and then
>> enable it will also work for me.
>>
>> Can it be done ?
>>
>> Thanks,
>> Shashwat.
>>
>
> Hi Shashwat
>
> I am no PG guru but I guess that, since "viewname" is a view and not a
> table, you should use "alter view" and not "alter table". See:
> http://www.postgresql.org/docs/9.3/static/sql-alterview.html
>
>
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: [NOVICE] Drop or disable or bypass "_return" rule on select on a view.
2015-05-28 06:23 Drop or disable or bypass "_return" rule on select on a view. Shashwat Arghode <shashwatarghode@gmail.com>
2015-05-28 09:19 ` Re: Drop or disable or bypass "_return" rule on select on a view. Vincenzo Campanella <vinz65@gmail.com>
2015-05-28 09:44 ` Re: Drop or disable or bypass "_return" rule on select on a view. Shashwat Arghode <shashwatarghode@gmail.com>
@ 2015-05-28 19:34 ` Faisal Karim <faisalk@furniture-pro.com>
2015-05-29 06:13 ` Re: [SQL] Drop or disable or bypass "_return" rule on select on a view. Shashwat Arghode <shashwatarghode@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Faisal Karim @ 2015-05-28 19:34 UTC (permalink / raw)
To: Shashwat Arghode <shashwatarghode@gmail.com>; Vincenzo Campanella <vinz65@gmail.com>; +Cc: pgsql-novice@postgresql.org <pgsql-novice@postgresql.org>; pgsql-sql
Did you mean ALTER VIEW Viewname instead of ALTER TABLE Viewname?
From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Shashwat Arghode
Sent: Thursday, May 28, 2015 4:45 AM
To: Vincenzo Campanella
Cc: pgsql-novice@postgresql.org; pgsql-sql@postgresql.org
Subject: Re: [SQL] [NOVICE] Drop or disable or bypass "_return" rule on select on a view.
Hi Vincenzo,
There is no option like disable rule in alter view documentation.
Still after trying it, it gives same error as follows.
ERROR: "viewname" is not a table
Thanks,
Shashwat.
On Thu, May 28, 2015 at 2:49 PM, Vincenzo Campanella <vinz65@gmail.com<mailto:vinz65@gmail.com>> wrote:
Il 28.05.2015 08:23, Shashwat Arghode ha scritto:
Hi all,
I am using postgres 9.3.4 and have an on_select rule "_return" on a view.
I want to drop or disable or bypass that rule.
Is there any way it can be done without dropping the view??
Because when i try to drop it, it returns
ERROR: cannot drop rule _RETURN on view viewname because view viewname requires it
HINT: You can drop viewname instead.
CONTEXT: SQL statement "drop rule "_RETURN" on viewname"
PL/pgSQL function inline_code_block line 12 at EXECUTE statement
and i don't want to drop the view.
when i try to disable it using :
alter table viewname disable rule _return
it returns
ERROR: "viewname" is not a table
Bypassing rule for a single query or disabling it for some time and then enable it will also work for me.
Can it be done ?
Thanks,
Shashwat.
Hi Shashwat
I am no PG guru but I guess that, since "viewname" is a view and not a table, you should use "alter view" and not "alter table". See:
http://www.postgresql.org/docs/9.3/static/sql-alterview.html
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: [SQL] Drop or disable or bypass "_return" rule on select on a view.
2015-05-28 06:23 Drop or disable or bypass "_return" rule on select on a view. Shashwat Arghode <shashwatarghode@gmail.com>
2015-05-28 09:19 ` Re: Drop or disable or bypass "_return" rule on select on a view. Vincenzo Campanella <vinz65@gmail.com>
2015-05-28 09:44 ` Re: Drop or disable or bypass "_return" rule on select on a view. Shashwat Arghode <shashwatarghode@gmail.com>
2015-05-28 19:34 ` Re: [NOVICE] Drop or disable or bypass "_return" rule on select on a view. Faisal Karim <faisalk@furniture-pro.com>
@ 2015-05-29 06:13 ` Shashwat Arghode <shashwatarghode@gmail.com>
0 siblings, 0 replies; 8+ messages in thread
From: Shashwat Arghode @ 2015-05-29 06:13 UTC (permalink / raw)
To: Faisal Karim <faisalk@furniture-pro.com>; +Cc: Vincenzo Campanella <vinz65@gmail.com>; pgsql-novice@postgresql.org <pgsql-novice@postgresql.org>; pgsql-sql
Oh yes. i should update the rule and redirect it accordingly as dropping or
disabling it will leave the view non-functional.
Thanks tom and merlin.
Thanks all.
-shashwat.
On Fri, May 29, 2015 at 1:04 AM, Faisal Karim <faisalk@furniture-pro.com>
wrote:
> Did you mean ALTER VIEW Viewname instead of ALTER TABLE Viewname?
>
>
>
> *From:* pgsql-sql-owner@postgresql.org [mailto:
> pgsql-sql-owner@postgresql.org] *On Behalf Of *Shashwat Arghode
> *Sent:* Thursday, May 28, 2015 4:45 AM
> *To:* Vincenzo Campanella
> *Cc:* pgsql-novice@postgresql.org; pgsql-sql@postgresql.org
> *Subject:* Re: [SQL] [NOVICE] Drop or disable or bypass "_return" rule on
> select on a view.
>
>
>
> Hi Vincenzo,
> There is no option like disable rule in alter view documentation.
> Still after trying it, it gives same error as follows.
>
> ERROR: "viewname" is not a table
>
> Thanks,
>
> Shashwat.
>
>
>
> On Thu, May 28, 2015 at 2:49 PM, Vincenzo Campanella <vinz65@gmail.com>
> wrote:
>
> Il 28.05.2015 08:23, Shashwat Arghode ha scritto:
>
> Hi all,
> I am using postgres 9.3.4 and have an on_select rule "_return" on a view.
> I want to drop or disable or bypass that rule.
> Is there any way it can be done without dropping the view??
>
> Because when i try to drop it, it returns
>
> ERROR: cannot drop rule _RETURN on view viewname because view viewname
> requires it
> HINT: You can drop viewname instead.
> CONTEXT: SQL statement "drop rule "_RETURN" on viewname"
> PL/pgSQL function inline_code_block line 12 at EXECUTE statement
>
> and i don't want to drop the view.
>
> when i try to disable it using :
> alter table viewname disable rule _return
> it returns
> ERROR: "viewname" is not a table
>
>
> Bypassing rule for a single query or disabling it for some time and then
> enable it will also work for me.
>
> Can it be done ?
>
> Thanks,
> Shashwat.
>
>
>
> Hi Shashwat
>
> I am no PG guru but I guess that, since "viewname" is a view and not a
> table, you should use "alter view" and not "alter table". See:
> http://www.postgresql.org/docs/9.3/static/sql-alterview.html
>
>
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Drop or disable or bypass "_return" rule on select on a view.
2015-05-28 06:23 Drop or disable or bypass "_return" rule on select on a view. Shashwat Arghode <shashwatarghode@gmail.com>
@ 2015-05-28 10:34 ` Luca Ferrari <fluca1978@infinito.it>
2 siblings, 0 replies; 8+ messages in thread
From: Luca Ferrari @ 2015-05-28 10:34 UTC (permalink / raw)
To: Shashwat Arghode <shashwatarghode@gmail.com>; +Cc: pgsql-novice@postgresql.org <pgsql-novice@postgresql.org>; pgsql-sql
On Thu, May 28, 2015 at 8:23 AM, Shashwat Arghode
<shashwatarghode@gmail.com> wrote:
> Hi all,
> I am using postgres 9.3.4 and have an on_select rule "_return" on a view.
> I want to drop or disable or bypass that rule.
> Is there any way it can be done without dropping the view??
>
> Because when i try to drop it, it returns
>
> ERROR: cannot drop rule _RETURN on view viewname because view viewname
> requires it
> HINT: You can drop viewname instead.
> CONTEXT: SQL statement "drop rule "_RETURN" on viewname"
> PL/pgSQL function inline_code_block line 12 at EXECUTE statement
I suspect you have messed up names, creating the rule on the target
table instead of another one used as a placeholder.
See the example here:
http://www.postgresql.org/docs/current/static/rules-views.html
Luca
--
Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Drop or disable or bypass "_return" rule on select on a view.
2015-05-28 06:23 Drop or disable or bypass "_return" rule on select on a view. Shashwat Arghode <shashwatarghode@gmail.com>
@ 2015-05-28 13:53 ` Tom Lane <tgl@sss.pgh.pa.us>
2015-05-28 14:17 ` Re: Drop or disable or bypass "_return" rule on select on a view. Merlin Moncure <mmoncure@gmail.com>
2 siblings, 1 reply; 8+ messages in thread
From: Tom Lane @ 2015-05-28 13:53 UTC (permalink / raw)
To: Shashwat Arghode <shashwatarghode@gmail.com>; +Cc: pgsql-novice@postgresql.org; pgsql-sql
Shashwat Arghode <shashwatarghode@gmail.com> writes:
> I am using postgres 9.3.4 and have an on_select rule "_return" on a view.
> I want to drop or disable or bypass that rule.
> Is there any way it can be done without dropping the view??
No. I don't exactly see the point, either --- what do you imagine a view
without an ON SELECT rule would be good for?
Perhaps what you want is to replace the view with CREATE OR REPLACE VIEW,
which is basically equivalent to updating its ON SELECT rule. But simply
dropping the rule without immediately replacing it would leave the view
nonfunctional.
regards, tom lane
--
Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Drop or disable or bypass "_return" rule on select on a view.
2015-05-28 06:23 Drop or disable or bypass "_return" rule on select on a view. Shashwat Arghode <shashwatarghode@gmail.com>
2015-05-28 13:53 ` Re: Drop or disable or bypass "_return" rule on select on a view. Tom Lane <tgl@sss.pgh.pa.us>
@ 2015-05-28 14:17 ` Merlin Moncure <mmoncure@gmail.com>
0 siblings, 0 replies; 8+ messages in thread
From: Merlin Moncure @ 2015-05-28 14:17 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Shashwat Arghode <shashwatarghode@gmail.com>; pgsql novice <pgsql-novice@postgresql.org>; pgsql-sql
On Thu, May 28, 2015 at 8:53 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Shashwat Arghode <shashwatarghode@gmail.com> writes:
>> I am using postgres 9.3.4 and have an on_select rule "_return" on a view.
>> I want to drop or disable or bypass that rule.
>> Is there any way it can be done without dropping the view??
>
> No. I don't exactly see the point, either --- what do you imagine a view
> without an ON SELECT rule would be good for?
>
> Perhaps what you want is to replace the view with CREATE OR REPLACE VIEW,
> which is basically equivalent to updating its ON SELECT rule. But simply
> dropping the rule without immediately replacing it would leave the view
> nonfunctional.
Yeah. If disabling the view is truly what's desired, and disabling a
view is defined as returning no data, you'd want to change:
CREATE OR REPLACE VIEW v AS SELECT ....
with
CREATE OR REPLACE VIEW v AS SELECT .... LIMIT 0;
Another option of course would be to drop it, but that would require
dealing with dependencies. Still another option would be to have any
query against the view return an immediate exception:
CREATE OR REPLACE VIEW v AS SELECT .... WHERE (SELECT false FROM Error('test'));
Error() being a thin wrapper to plpgsql 'raise exception'.
merlin
--
Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2015-05-29 06:13 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2015-05-28 06:23 Drop or disable or bypass "_return" rule on select on a view. Shashwat Arghode <shashwatarghode@gmail.com>
2015-05-28 09:19 ` Vincenzo Campanella <vinz65@gmail.com>
2015-05-28 09:44 ` Shashwat Arghode <shashwatarghode@gmail.com>
2015-05-28 19:34 ` Faisal Karim <faisalk@furniture-pro.com>
2015-05-29 06:13 ` Shashwat Arghode <shashwatarghode@gmail.com>
2015-05-28 10:34 ` Luca Ferrari <fluca1978@infinito.it>
2015-05-28 13:53 ` Tom Lane <tgl@sss.pgh.pa.us>
2015-05-28 14:17 ` Merlin Moncure <mmoncure@gmail.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox