On 16 January 2013 17:20, Kevin Grittner 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;

--
Thom

Search Discussions

Discussion Posts

Previous

Follow ups

Related Discussions

People

Translate

site design / logo © 2021 Grokbase