pg_get_viewdef() drops casts, causing broken definitions

PostgreSQL 9.5.2.

I ran into this while doing a pgupgrade; it refused to re-import a view that was operating correctly on 9.2.

Summary: The view text returned by pg_get_viewdef() strips some casts, which can result in an error in trying to recreate the view. This means you can't successfully recreate those views from a pg_dump.

Recreated:

xof=# drop table b;
DROP TABLE
xof=# CREATE TABLE a (i integer);
CREATE TABLE
xof=# INSERT INTO a SELECT generate_series(1,100);
INSERT 0 100
xof=# CREATE TABLE b (i integer);
CREATE TABLE
xof=# INSERT INTO b values(100);
INSERT 0 1
xof=# CREATE VIEW vb AS SELECT i FROM b WHERE i = ANY((SELECT array_agg(i) FROM a)::integer[]);
CREATE VIEW
xof=# SELECT * FROM vb;
i
-----
100
(1 row)

xof=# select pg_get_viewdef('vb');
pg_get_viewdef
----------------------------------------------------------
SELECT b.i +
FROM b +
WHERE (b.i = ANY (( SELECT array_agg(a.i) AS array_agg+
FROM a)));
(1 row)

Note the missing cast.

It's missing from pg_dump as well, of course, since it uses that same function:

--
-- Name: vb; Type: VIEW; Schema: public; Owner: xof
--

CREATE VIEW vb AS
SELECT b.i
FROM b
WHERE (b.i = ANY (( SELECT array_agg(a.i) AS array_agg
FROM a)));

ALTER TABLE vb OWNER TO xof;

But recreating it from that definition results in an error:

xof=# drop view vb;
DROP VIEW
xof=# CREATE VIEW vb AS
xof-# SELECT b.i
xof-# FROM b
xof-# WHERE (b.i = ANY (( SELECT array_agg(a.i) AS array_agg
xof(# FROM a)));
ERROR: operator does not exist: integer = integer[]
LINE 4: WHERE (b.i = ANY (( SELECT array_agg(a.i) AS array_agg
^
HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.

--
-- Christophe Pettus
xof@thebuild.com

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs