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=3D# drop table b;
DROP TABLE
xof=3D# CREATE TABLE a (i integer);
CREATE TABLE
xof=3D# INSERT INTO a SELECT generate_series(1,100);
INSERT 0 100
xof=3D# CREATE TABLE b (i integer);
CREATE TABLE
xof=3D# INSERT INTO b values(100);
INSERT 0 1
xof=3D# CREATE VIEW vb AS SELECT i FROM b WHERE i =3D ANY((SELECT =
array_agg(i) FROM a)::integer[]);
CREATE VIEW
xof=3D# SELECT * FROM vb;
i =20
-----
100
(1 row)
xof=3D# select pg_get_viewdef('vb');
pg_get_viewdef =20
----------------------------------------------------------
SELECT b.i +
FROM b +
WHERE (b.i =3D 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 =3D 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=3D# drop view vb;
DROP VIEW
xof=3D# CREATE VIEW vb AS
xof-# SELECT b.i
xof-# FROM b
xof-# WHERE (b.i =3D ANY (( SELECT array_agg(a.i) AS array_agg
xof(# FROM a)));
ERROR: operator does not exist: integer =3D integer[]
LINE 4: WHERE (b.i =3D 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