The following bug has been logged on the website:
Bug reference: 7553
Logged by: Lloyd Albin
Email address: [email protected]
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)
--
Sent via pgsql-bugs mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
