pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedConverting TBL->View complaining about indexes
2+ messages / 2 participants
[nested] [flat]
* Converting TBL->View complaining about indexes
@ 2005-06-24 15:49 KÖPFERL Robert <robert.koepferl@sonorys.at>
0 siblings, 1 reply; 2+ messages in thread
From: KÖPFERL Robert @ 2005-06-24 15:49 UTC (permalink / raw)
To: ; +Cc: pgsql-sql
Hi,
I'm currently trying to make a table (where many fcns depend on) become a
view.
Thus I did a _truncate_ and lots of _alter table drop constraint_ and _drop
index_
and then
CREATE OR REPLACE RULE "_RETURN" AS
ON SELECT TO "smsMessagesrewtet"
DO INSTEAD
SELECT "MessageID",.....
Postgres keeps returning
ERROR: could not convert table "smsMessagesrewtet" to a view because it has
indexes
However I am sure there are no indexes nor PK or any constraints. What else
could it be?
Thanks
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Converting TBL->View complaining about indexes
@ 2005-06-24 17:44 Michael Fuhr <mike@fuhr.org>
parent: KÖPFERL Robert <robert.koepferl@sonorys.at>
0 siblings, 0 replies; 2+ messages in thread
From: Michael Fuhr @ 2005-06-24 17:44 UTC (permalink / raw)
To: KÖPFERL Robert <robert.koepferl@sonorys.at>; +Cc: pgsql-sql
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/
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2005-06-24 17:44 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-06-24 15:49 Converting TBL->View complaining about indexes KÖPFERL Robert <robert.koepferl@sonorys.at>
2005-06-24 17:44 ` Michael Fuhr <mike@fuhr.org>
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