BUG #7553: Un-executable view definitions in pg_catalog.pg_views in versions 8.3.x-9.2.0

The following bug has been logged on the website:

Bug reference:      7553
Logged by:          Lloyd Albin
Email address:      lalbin@fhcrc.org
PostgreSQL version: 9.0.7
Operating system:   SUSE Linux (64-bit)
Description:        =


I have run across a situation where we are changing schema and table names
that a view references. The view is still viewable with a select statement
but if you try and execute the view definition contained within the
pg_catalog.pg_views, then it fails. I have tested this on the following
versions:
9.3.9 Redhat 4.1.2 (64-bit)
9.3.20 Windows 7 (32-bit)
8.4.4 SUSE Linux (32-bit)
8.4.13 Windows 7 (32-bit)
9.0.7 SUSE Linux (64-bit)
9.0.9 Windows 7 (64-bit)
9.1.5 Windows 7 (64-bit)
9.2Beta1 Windows 7 (64-bit)
9.2.0 Windows 7 (64-bit)

I have been able to write this script to demo the failure.



-- Start test script
CREATE SCHEMA schema_a;
CREATE SCHEMA schema_b;
CREATE SCHEMA schema_c;

CREATE TABLE schema_a.table_a (
                id            varchar(11),
    field1 varchar(10)
);

CREATE TABLE schema_b.table_a (
                id            varchar(11),
    field1 varchar(10)
);

CREATE TABLE schema_b.table_b (
                id            varchar(11)
);

INSERT INTO schema_a.table_a VALUES ('test1', 'test2');
INSERT INTO schema_b.table_a VALUES ('test1', 'test3');
INSERT INTO schema_b.table_b VALUES ('test1');

CREATE OR REPLACE VIEW public.view_b AS =

SELECT =

                schema_a.table_a.field1,
                schema_b.table_a.field1 AS field2
FROM schema_b.table_b
LEFT JOIN schema_b.table_a
                ON schema_b.table_b.id =3D schema_b.table_a.id
LEFT JOIN schema_a.table_a
                ON schema_a.table_a.id =3D schema_b.table_b.id;

SELECT * FROM public.view_b;

CREATE OR REPLACE FUNCTION schema_c.function_a ()
RETURNS void AS $$
DECLARE
  def_row RECORD;
BEGIN
    SELECT definition INTO def_row FROM pg_catalog.pg_views WHERE viewname =3D
'view_b';
    EXECUTE def_row.definition;
END;
$$ LANGUAGE plpgsql;

SELECT schema_c.function_a();

ALTER TABLE schema_a.table_a RENAME TO table_d;

ALTER TABLE schema_a.table_d SET SCHEMA schema_c;

ALTER TABLE schema_b.table_a RENAME TO table_e;

ALTER TABLE schema_b.table_e SET SCHEMA schema_c;

ALTER TABLE schema_b.table_b RENAME TO table_f;

ALTER TABLE schema_b.table_f SET SCHEMA schema_c;

-- Use with Postgres 8.3
--ALTER TABLE public.view_b SET SCHEMA schema_c;
-- Use with Postgres 8.4+
ALTER VIEW public.view_b SET SCHEMA schema_c;

SELECT * FROM schema_c.view_b;

SELECT schema_c.function_a();
-- End test script



When executed you get the following error:

ERROR:  invalid reference to FROM-clause entry for table "table_a"
LINE 1: ...hema_c.table_e table_a ON (((table_b.id)::text =3D (schema_c.t...
                                                             ^
HINT:  There is an entry for table "table_a", but it cannot be referenced
from this part of the query.
QUERY:  SELECT schema_c.table_a.field1, schema_c.table_a.field1 AS field2
FROM ((schema_c.table_f table_b LEFT JOIN schema_c.table_e table_a ON
(((table_b.id)::text =3D (schema_c.table_a.id)::text))) LEFT JOIN
schema_c.table_d table_a ON (((schema_c.table_a.id)::text =3D
(table_b.id)::text)));
CONTEXT:  PL/pgSQL function schema_c.function_a() line 6 at EXECUTE
statement


Lloyd Albin
Statistical Center for HIV/AIDS Research and Prevention (SCHARP)
Vaccine and Infectious Disease Division (VIDD)
Fred Hutchinson Cancer Research Center (FHCRC)