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(at)fhcrc(dot)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 = schema_b.table_a.id
LEFT JOIN schema_a.table_a
ON schema_a.table_a.id = 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 =
'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 = (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 = (schema_c.table_a.id)::text))) LEFT JOIN
schema_c.table_d table_a ON (((schema_c.table_a.id)::text =
(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)