From: Michael Fuhr <mike@fuhr.org>
To: KÖPFERL Robert <robert.koepferl@sonorys.at>
Cc: PgSQL - SQL <pgsql-sql@postgresql.org>
Subject: Re: Converting TBL->View complaining about indexes
Date: Fri, 24 Jun 2005 11:44:05 -0600
Message-ID: <20050624174404.GA46407@winnie.fuhr.org> (raw)
In-Reply-To: <ED4E30DD9C43D5118DFB00508BBBA76EB1679E@neptun.sonorys.at>
References: <ED4E30DD9C43D5118DFB00508BBBA76EB1679E@neptun.sonorys.at>
On Fri, Jun 24, 2005 at 05:49:02PM +0200, KÖPFERL Robert wrote:
>
> ERROR: could not convert table "smsMessagesrewtet" to a view because it has
> indexes
See the comments for pg_class.relhasindex:
http://www.postgresql.org/docs/8.0/static/catalog-pg-class.html
"True if this is a table and it has (or recently had) any indexes.
This is set by CREATE INDEX, but not cleared immediately by DROP
INDEX. VACUUM clears relhasindex if it finds the table has no
indexes."
test=> CREATE TABLE foo (x integer);
CREATE TABLE
test=> CREATE INDEX foo_x_idx ON foo (x);
CREATE INDEX
test=> DROP INDEX foo_x_idx;
DROP INDEX
test=> CREATE RULE "_RETURN" AS ON SELECT TO foo
test-> DO INSTEAD SELECT 123::integer AS x;
ERROR: could not convert table "foo" to a view because it has indexes
test=> VACUUM foo;
VACUUM
test=> CREATE RULE "_RETURN" AS ON SELECT TO foo
test-> DO INSTEAD SELECT 123::integer AS x;
CREATE RULE
test=> SELECT * FROM foo;
x
-----
123
(1 row)
test=> \d foo
View "public.foo"
Column | Type | Modifiers
--------+---------+-----------
x | integer |
View definition:
SELECT 123 AS x;
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
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: mike@fuhr.org, robert.koepferl@sonorys.at
Subject: Re: Converting TBL->View complaining about indexes
In-Reply-To: <20050624174404.GA46407@winnie.fuhr.org>
* 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