On 16 January 2013 17:25, Thom Brown <t...@linux.com> wrote: > On 16 January 2013 17:20, Kevin Grittner <kgri...@mail.com> wrote: > >> Thom Brown wrote: >> >> > Some weirdness: >> > >> > postgres=# CREATE VIEW v_test2 AS SELECT 1 moo; >> > CREATE VIEW >> > postgres=# CREATE MATERIALIZED VIEW mv_test2 AS SELECT moo, 2*moo FROM >> > v_test2 UNION ALL SELECT moo, 3*moo FROM v_test2; >> > SELECT 2 >> > postgres=# \d+ mv_test2 >> > Materialized view "public.mv_test2" >> > Column | Type | Modifiers | Storage | Stats target | Description >> > ----------+---------+-----------+---------+--------------+------------- >> > moo | integer | | plain | | >> > ?column? | integer | | plain | | >> > View definition: >> > SELECT "*SELECT* 1".moo, "*SELECT* 1"."?column?"; >> >> You are very good at coming up with these, Thom! >> >> Will investigate. >> >> Can you confirm that *selecting* from the MV works as you would >> expect; it is just the presentation in \d+ that's a problem? >> > > Yes, nothing wrong with using the MV, or refreshing it: > > postgres=# TABLE mv_test2; > moo | ?column? > -----+---------- > 1 | 2 > 1 | 3 > (2 rows) > > postgres=# SELECT * FROM mv_test2; > moo | ?column? > -----+---------- > 1 | 2 > 1 | 3 > (2 rows) > > postgres=# REFRESH MATERIALIZED VIEW mv_test2; > REFRESH MATERIALIZED VIEW > > But a pg_dump of the MV has the same issue as the view definition: > > -- > -- Name: mv_test2; Type: MATERIALIZED VIEW; Schema: public; Owner: thom; > Tablespace: > -- > > CREATE MATERIALIZED VIEW mv_test2 ( > moo, > "?column?" > ) AS > SELECT "*SELECT* 1".moo, "*SELECT* 1"."?column?" > WITH NO DATA; >
A separate issue is with psql tab-completion: postgres=# COMMENT ON MATERIALIZED VIEW ^IIS This should be offering MV names instead of prematurely providing the "IS" keyword. -- Thom
