Date: Fri, 24 Jun 2005 11:44:05 -0600
From: Michael Fuhr <mike@fuhr.org>
To: =?iso-8859-1?Q?K=D6PFERL?= Robert <robert.koepferl@sonorys.at>
Cc: PgSQL - SQL <pgsql-sql@postgresql.org>
Subject: Re: Converting TBL->View complaining about indexes
Message-ID: <20050624174404.GA46407@winnie.fuhr.org>
References: <ED4E30DD9C43D5118DFB00508BBBA76EB1679E@neptun.sonorys.at>
Mime-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <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/