diff options
Diffstat (limited to 'src/test/regress/expected/create_view.out')
| -rw-r--r-- | src/test/regress/expected/create_view.out | 2257 |
1 files changed, 2257 insertions, 0 deletions
diff --git a/src/test/regress/expected/create_view.out b/src/test/regress/expected/create_view.out new file mode 100644 index 0000000..63c3c2a --- /dev/null +++ b/src/test/regress/expected/create_view.out @@ -0,0 +1,2257 @@ +-- +-- CREATE_VIEW +-- Virtual class definitions +-- (this also tests the query rewrite system) +-- +-- directory paths and dlsuffix are passed to us in environment variables +\getenv abs_srcdir PG_ABS_SRCDIR +\getenv libdir PG_LIBDIR +\getenv dlsuffix PG_DLSUFFIX +\set regresslib :libdir '/regress' :dlsuffix +CREATE FUNCTION interpt_pp(path, path) + RETURNS point + AS :'regresslib' + LANGUAGE C STRICT; +CREATE TABLE real_city ( + pop int4, + cname text, + outline path +); +\set filename :abs_srcdir '/data/real_city.data' +COPY real_city FROM :'filename'; +ANALYZE real_city; +SELECT * + INTO TABLE ramp + FROM ONLY road + WHERE name ~ '.*Ramp'; +CREATE VIEW street AS + SELECT r.name, r.thepath, c.cname AS cname + FROM ONLY road r, real_city c + WHERE c.outline ?# r.thepath; +CREATE VIEW iexit AS + SELECT ih.name, ih.thepath, + interpt_pp(ih.thepath, r.thepath) AS exit + FROM ihighway ih, ramp r + WHERE ih.thepath ?# r.thepath; +CREATE VIEW toyemp AS + SELECT name, age, location, 12*salary AS annualsal + FROM emp; +-- Test comments +COMMENT ON VIEW noview IS 'no view'; +ERROR: relation "noview" does not exist +COMMENT ON VIEW toyemp IS 'is a view'; +COMMENT ON VIEW toyemp IS NULL; +-- These views are left around mainly to exercise special cases in pg_dump. +CREATE TABLE view_base_table (key int PRIMARY KEY, data varchar(20)); +CREATE VIEW key_dependent_view AS + SELECT * FROM view_base_table GROUP BY key; +ALTER TABLE view_base_table DROP CONSTRAINT view_base_table_pkey; -- fails +ERROR: cannot drop constraint view_base_table_pkey on table view_base_table because other objects depend on it +DETAIL: view key_dependent_view depends on constraint view_base_table_pkey on table view_base_table +HINT: Use DROP ... CASCADE to drop the dependent objects too. +CREATE VIEW key_dependent_view_no_cols AS + SELECT FROM view_base_table GROUP BY key HAVING length(data) > 0; +-- +-- CREATE OR REPLACE VIEW +-- +CREATE TABLE viewtest_tbl (a int, b int, c numeric(10,1), d text COLLATE "C"); +COPY viewtest_tbl FROM stdin; +CREATE OR REPLACE VIEW viewtest AS + SELECT * FROM viewtest_tbl; +CREATE OR REPLACE VIEW viewtest AS + SELECT * FROM viewtest_tbl WHERE a > 10; +SELECT * FROM viewtest; + a | b | c | d +----+----+-----+------- + 15 | 20 | 3.3 | xyzz + 20 | 25 | 4.4 | xyzzy +(2 rows) + +CREATE OR REPLACE VIEW viewtest AS + SELECT a, b, c, d FROM viewtest_tbl WHERE a > 5 ORDER BY b DESC; +SELECT * FROM viewtest; + a | b | c | d +----+----+-----+------- + 20 | 25 | 4.4 | xyzzy + 15 | 20 | 3.3 | xyzz + 10 | 15 | 2.2 | xyz +(3 rows) + +-- should fail +CREATE OR REPLACE VIEW viewtest AS + SELECT a FROM viewtest_tbl WHERE a <> 20; +ERROR: cannot drop columns from view +-- should fail +CREATE OR REPLACE VIEW viewtest AS + SELECT 1, * FROM viewtest_tbl; +ERROR: cannot change name of view column "a" to "?column?" +HINT: Use ALTER VIEW ... RENAME COLUMN ... to change name of view column instead. +-- should fail +CREATE OR REPLACE VIEW viewtest AS + SELECT a, b::numeric, c, d FROM viewtest_tbl; +ERROR: cannot change data type of view column "b" from integer to numeric +-- should fail +CREATE OR REPLACE VIEW viewtest AS + SELECT a, b, c::numeric(10,2), d FROM viewtest_tbl; +ERROR: cannot change data type of view column "c" from numeric(10,1) to numeric(10,2) +-- should fail +CREATE OR REPLACE VIEW viewtest AS + SELECT a, b, c, d COLLATE "POSIX" FROM viewtest_tbl; +ERROR: cannot change collation of view column "d" from "C" to "POSIX" +-- should work +CREATE OR REPLACE VIEW viewtest AS + SELECT a, b, c, d, 0 AS e FROM viewtest_tbl; +DROP VIEW viewtest; +DROP TABLE viewtest_tbl; +-- tests for temporary views +CREATE SCHEMA temp_view_test + CREATE TABLE base_table (a int, id int) + CREATE TABLE base_table2 (a int, id int); +SET search_path TO temp_view_test, public; +CREATE TEMPORARY TABLE temp_table (a int, id int); +-- should be created in temp_view_test schema +CREATE VIEW v1 AS SELECT * FROM base_table; +-- should be created in temp object schema +CREATE VIEW v1_temp AS SELECT * FROM temp_table; +NOTICE: view "v1_temp" will be a temporary view +-- should be created in temp object schema +CREATE TEMP VIEW v2_temp AS SELECT * FROM base_table; +-- should be created in temp_views schema +CREATE VIEW temp_view_test.v2 AS SELECT * FROM base_table; +-- should fail +CREATE VIEW temp_view_test.v3_temp AS SELECT * FROM temp_table; +NOTICE: view "v3_temp" will be a temporary view +ERROR: cannot create temporary relation in non-temporary schema +-- should fail +CREATE SCHEMA test_view_schema + CREATE TEMP VIEW testview AS SELECT 1; +ERROR: cannot create temporary relation in non-temporary schema +-- joins: if any of the join relations are temporary, the view +-- should also be temporary +-- should be non-temp +CREATE VIEW v3 AS + SELECT t1.a AS t1_a, t2.a AS t2_a + FROM base_table t1, base_table2 t2 + WHERE t1.id = t2.id; +-- should be temp (one join rel is temp) +CREATE VIEW v4_temp AS + SELECT t1.a AS t1_a, t2.a AS t2_a + FROM base_table t1, temp_table t2 + WHERE t1.id = t2.id; +NOTICE: view "v4_temp" will be a temporary view +-- should be temp +CREATE VIEW v5_temp AS + SELECT t1.a AS t1_a, t2.a AS t2_a, t3.a AS t3_a + FROM base_table t1, base_table2 t2, temp_table t3 + WHERE t1.id = t2.id and t2.id = t3.id; +NOTICE: view "v5_temp" will be a temporary view +-- subqueries +CREATE VIEW v4 AS SELECT * FROM base_table WHERE id IN (SELECT id FROM base_table2); +CREATE VIEW v5 AS SELECT t1.id, t2.a FROM base_table t1, (SELECT * FROM base_table2) t2; +CREATE VIEW v6 AS SELECT * FROM base_table WHERE EXISTS (SELECT 1 FROM base_table2); +CREATE VIEW v7 AS SELECT * FROM base_table WHERE NOT EXISTS (SELECT 1 FROM base_table2); +CREATE VIEW v8 AS SELECT * FROM base_table WHERE EXISTS (SELECT 1); +CREATE VIEW v6_temp AS SELECT * FROM base_table WHERE id IN (SELECT id FROM temp_table); +NOTICE: view "v6_temp" will be a temporary view +CREATE VIEW v7_temp AS SELECT t1.id, t2.a FROM base_table t1, (SELECT * FROM temp_table) t2; +NOTICE: view "v7_temp" will be a temporary view +CREATE VIEW v8_temp AS SELECT * FROM base_table WHERE EXISTS (SELECT 1 FROM temp_table); +NOTICE: view "v8_temp" will be a temporary view +CREATE VIEW v9_temp AS SELECT * FROM base_table WHERE NOT EXISTS (SELECT 1 FROM temp_table); +NOTICE: view "v9_temp" will be a temporary view +-- a view should also be temporary if it references a temporary view +CREATE VIEW v10_temp AS SELECT * FROM v7_temp; +NOTICE: view "v10_temp" will be a temporary view +CREATE VIEW v11_temp AS SELECT t1.id, t2.a FROM base_table t1, v10_temp t2; +NOTICE: view "v11_temp" will be a temporary view +CREATE VIEW v12_temp AS SELECT true FROM v11_temp; +NOTICE: view "v12_temp" will be a temporary view +-- a view should also be temporary if it references a temporary sequence +CREATE SEQUENCE seq1; +CREATE TEMPORARY SEQUENCE seq1_temp; +CREATE VIEW v9 AS SELECT seq1.is_called FROM seq1; +CREATE VIEW v13_temp AS SELECT seq1_temp.is_called FROM seq1_temp; +NOTICE: view "v13_temp" will be a temporary view +SELECT relname FROM pg_class + WHERE relname LIKE 'v_' + AND relnamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'temp_view_test') + ORDER BY relname; + relname +--------- + v1 + v2 + v3 + v4 + v5 + v6 + v7 + v8 + v9 +(9 rows) + +SELECT relname FROM pg_class + WHERE relname LIKE 'v%' + AND relnamespace IN (SELECT oid FROM pg_namespace WHERE nspname LIKE 'pg_temp%') + ORDER BY relname; + relname +---------- + v10_temp + v11_temp + v12_temp + v13_temp + v1_temp + v2_temp + v4_temp + v5_temp + v6_temp + v7_temp + v8_temp + v9_temp +(12 rows) + +CREATE SCHEMA testviewschm2; +SET search_path TO testviewschm2, public; +CREATE TABLE t1 (num int, name text); +CREATE TABLE t2 (num2 int, value text); +CREATE TEMP TABLE tt (num2 int, value text); +CREATE VIEW nontemp1 AS SELECT * FROM t1 CROSS JOIN t2; +CREATE VIEW temporal1 AS SELECT * FROM t1 CROSS JOIN tt; +NOTICE: view "temporal1" will be a temporary view +CREATE VIEW nontemp2 AS SELECT * FROM t1 INNER JOIN t2 ON t1.num = t2.num2; +CREATE VIEW temporal2 AS SELECT * FROM t1 INNER JOIN tt ON t1.num = tt.num2; +NOTICE: view "temporal2" will be a temporary view +CREATE VIEW nontemp3 AS SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num2; +CREATE VIEW temporal3 AS SELECT * FROM t1 LEFT JOIN tt ON t1.num = tt.num2; +NOTICE: view "temporal3" will be a temporary view +CREATE VIEW nontemp4 AS SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num2 AND t2.value = 'xxx'; +CREATE VIEW temporal4 AS SELECT * FROM t1 LEFT JOIN tt ON t1.num = tt.num2 AND tt.value = 'xxx'; +NOTICE: view "temporal4" will be a temporary view +SELECT relname FROM pg_class + WHERE relname LIKE 'nontemp%' + AND relnamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'testviewschm2') + ORDER BY relname; + relname +---------- + nontemp1 + nontemp2 + nontemp3 + nontemp4 +(4 rows) + +SELECT relname FROM pg_class + WHERE relname LIKE 'temporal%' + AND relnamespace IN (SELECT oid FROM pg_namespace WHERE nspname LIKE 'pg_temp%') + ORDER BY relname; + relname +----------- + temporal1 + temporal2 + temporal3 + temporal4 +(4 rows) + +CREATE TABLE tbl1 ( a int, b int); +CREATE TABLE tbl2 (c int, d int); +CREATE TABLE tbl3 (e int, f int); +CREATE TABLE tbl4 (g int, h int); +CREATE TEMP TABLE tmptbl (i int, j int); +--Should be in testviewschm2 +CREATE VIEW pubview AS SELECT * FROM tbl1 WHERE tbl1.a +BETWEEN (SELECT d FROM tbl2 WHERE c = 1) AND (SELECT e FROM tbl3 WHERE f = 2) +AND EXISTS (SELECT g FROM tbl4 LEFT JOIN tbl3 ON tbl4.h = tbl3.f); +SELECT count(*) FROM pg_class where relname = 'pubview' +AND relnamespace IN (SELECT OID FROM pg_namespace WHERE nspname = 'testviewschm2'); + count +------- + 1 +(1 row) + +--Should be in temp object schema +CREATE VIEW mytempview AS SELECT * FROM tbl1 WHERE tbl1.a +BETWEEN (SELECT d FROM tbl2 WHERE c = 1) AND (SELECT e FROM tbl3 WHERE f = 2) +AND EXISTS (SELECT g FROM tbl4 LEFT JOIN tbl3 ON tbl4.h = tbl3.f) +AND NOT EXISTS (SELECT g FROM tbl4 LEFT JOIN tmptbl ON tbl4.h = tmptbl.j); +NOTICE: view "mytempview" will be a temporary view +SELECT count(*) FROM pg_class where relname LIKE 'mytempview' +And relnamespace IN (SELECT OID FROM pg_namespace WHERE nspname LIKE 'pg_temp%'); + count +------- + 1 +(1 row) + +-- +-- CREATE VIEW and WITH(...) clause +-- +CREATE VIEW mysecview1 + AS SELECT * FROM tbl1 WHERE a = 0; +CREATE VIEW mysecview2 WITH (security_barrier=true) + AS SELECT * FROM tbl1 WHERE a > 0; +CREATE VIEW mysecview3 WITH (security_barrier=false) + AS SELECT * FROM tbl1 WHERE a < 0; +CREATE VIEW mysecview4 WITH (security_barrier) + AS SELECT * FROM tbl1 WHERE a <> 0; +CREATE VIEW mysecview5 WITH (security_barrier=100) -- Error + AS SELECT * FROM tbl1 WHERE a > 100; +ERROR: invalid value for boolean option "security_barrier": 100 +CREATE VIEW mysecview6 WITH (invalid_option) -- Error + AS SELECT * FROM tbl1 WHERE a < 100; +ERROR: unrecognized parameter "invalid_option" +CREATE VIEW mysecview7 WITH (security_invoker=true) + AS SELECT * FROM tbl1 WHERE a = 100; +CREATE VIEW mysecview8 WITH (security_invoker=false, security_barrier=true) + AS SELECT * FROM tbl1 WHERE a > 100; +CREATE VIEW mysecview9 WITH (security_invoker) + AS SELECT * FROM tbl1 WHERE a < 100; +CREATE VIEW mysecview10 WITH (security_invoker=100) -- Error + AS SELECT * FROM tbl1 WHERE a <> 100; +ERROR: invalid value for boolean option "security_invoker": 100 +SELECT relname, relkind, reloptions FROM pg_class + WHERE oid in ('mysecview1'::regclass, 'mysecview2'::regclass, + 'mysecview3'::regclass, 'mysecview4'::regclass, + 'mysecview7'::regclass, 'mysecview8'::regclass, + 'mysecview9'::regclass) + ORDER BY relname; + relname | relkind | reloptions +------------+---------+------------------------------------------------ + mysecview1 | v | + mysecview2 | v | {security_barrier=true} + mysecview3 | v | {security_barrier=false} + mysecview4 | v | {security_barrier=true} + mysecview7 | v | {security_invoker=true} + mysecview8 | v | {security_invoker=false,security_barrier=true} + mysecview9 | v | {security_invoker=true} +(7 rows) + +CREATE OR REPLACE VIEW mysecview1 + AS SELECT * FROM tbl1 WHERE a = 256; +CREATE OR REPLACE VIEW mysecview2 + AS SELECT * FROM tbl1 WHERE a > 256; +CREATE OR REPLACE VIEW mysecview3 WITH (security_barrier=true) + AS SELECT * FROM tbl1 WHERE a < 256; +CREATE OR REPLACE VIEW mysecview4 WITH (security_barrier=false) + AS SELECT * FROM tbl1 WHERE a <> 256; +CREATE OR REPLACE VIEW mysecview7 + AS SELECT * FROM tbl1 WHERE a > 256; +CREATE OR REPLACE VIEW mysecview8 WITH (security_invoker=true) + AS SELECT * FROM tbl1 WHERE a < 256; +CREATE OR REPLACE VIEW mysecview9 WITH (security_invoker=false, security_barrier=true) + AS SELECT * FROM tbl1 WHERE a <> 256; +SELECT relname, relkind, reloptions FROM pg_class + WHERE oid in ('mysecview1'::regclass, 'mysecview2'::regclass, + 'mysecview3'::regclass, 'mysecview4'::regclass, + 'mysecview7'::regclass, 'mysecview8'::regclass, + 'mysecview9'::regclass) + ORDER BY relname; + relname | relkind | reloptions +------------+---------+------------------------------------------------ + mysecview1 | v | + mysecview2 | v | + mysecview3 | v | {security_barrier=true} + mysecview4 | v | {security_barrier=false} + mysecview7 | v | + mysecview8 | v | {security_invoker=true} + mysecview9 | v | {security_invoker=false,security_barrier=true} +(7 rows) + +-- Check that unknown literals are converted to "text" in CREATE VIEW, +-- so that we don't end up with unknown-type columns. +CREATE VIEW unspecified_types AS + SELECT 42 as i, 42.5 as num, 'foo' as u, 'foo'::unknown as u2, null as n; +\d+ unspecified_types + View "testviewschm2.unspecified_types" + Column | Type | Collation | Nullable | Default | Storage | Description +--------+---------+-----------+----------+---------+----------+------------- + i | integer | | | | plain | + num | numeric | | | | main | + u | text | | | | extended | + u2 | text | | | | extended | + n | text | | | | extended | +View definition: + SELECT 42 AS i, + 42.5 AS num, + 'foo'::text AS u, + 'foo'::text AS u2, + NULL::text AS n; + +SELECT * FROM unspecified_types; + i | num | u | u2 | n +----+------+-----+-----+--- + 42 | 42.5 | foo | foo | +(1 row) + +-- This test checks that proper typmods are assigned in a multi-row VALUES +CREATE VIEW tt1 AS + SELECT * FROM ( + VALUES + ('abc'::varchar(3), '0123456789', 42, 'abcd'::varchar(4)), + ('0123456789', 'abc'::varchar(3), 42.12, 'abc'::varchar(4)) + ) vv(a,b,c,d); +\d+ tt1 + View "testviewschm2.tt1" + Column | Type | Collation | Nullable | Default | Storage | Description +--------+----------------------+-----------+----------+---------+----------+------------- + a | character varying | | | | extended | + b | character varying | | | | extended | + c | numeric | | | | main | + d | character varying(4) | | | | extended | +View definition: + SELECT vv.a, + vv.b, + vv.c, + vv.d + FROM ( VALUES ('abc'::character varying(3),'0123456789'::character varying,42,'abcd'::character varying(4)), ('0123456789'::character varying,'abc'::character varying(3),42.12,'abc'::character varying(4))) vv(a, b, c, d); + +SELECT * FROM tt1; + a | b | c | d +------------+------------+-------+------ + abc | 0123456789 | 42 | abcd + 0123456789 | abc | 42.12 | abc +(2 rows) + +SELECT a::varchar(3) FROM tt1; + a +----- + abc + 012 +(2 rows) + +DROP VIEW tt1; +-- Test view decompilation in the face of relation renaming conflicts +CREATE TABLE tt1 (f1 int, f2 int, f3 text); +CREATE TABLE tx1 (x1 int, x2 int, x3 text); +CREATE TABLE temp_view_test.tt1 (y1 int, f2 int, f3 text); +CREATE VIEW aliased_view_1 AS + select * from tt1 + where exists (select 1 from tx1 where tt1.f1 = tx1.x1); +CREATE VIEW aliased_view_2 AS + select * from tt1 a1 + where exists (select 1 from tx1 where a1.f1 = tx1.x1); +CREATE VIEW aliased_view_3 AS + select * from tt1 + where exists (select 1 from tx1 a2 where tt1.f1 = a2.x1); +CREATE VIEW aliased_view_4 AS + select * from temp_view_test.tt1 + where exists (select 1 from tt1 where temp_view_test.tt1.y1 = tt1.f1); +\d+ aliased_view_1 + View "testviewschm2.aliased_view_1" + Column | Type | Collation | Nullable | Default | Storage | Description +--------+---------+-----------+----------+---------+----------+------------- + f1 | integer | | | | plain | + f2 | integer | | | | plain | + f3 | text | | | | extended | +View definition: + SELECT tt1.f1, + tt1.f2, + tt1.f3 + FROM tt1 + WHERE (EXISTS ( SELECT 1 + FROM tx1 + WHERE tt1.f1 = tx1.x1)); + +\d+ aliased_view_2 + View "testviewschm2.aliased_view_2" + Column | Type | Collation | Nullable | Default | Storage | Description +--------+---------+-----------+----------+---------+----------+------------- + f1 | integer | | | | plain | + f2 | integer | | | | plain | + f3 | text | | | | extended | +View definition: + SELECT a1.f1, + a1.f2, + a1.f3 + FROM tt1 a1 + WHERE (EXISTS ( SELECT 1 + FROM tx1 + WHERE a1.f1 = tx1.x1)); + +\d+ aliased_view_3 + View "testviewschm2.aliased_view_3" + Column | Type | Collation | Nullable | Default | Storage | Description +--------+---------+-----------+----------+---------+----------+------------- + f1 | integer | | | | plain | + f2 | integer | | | | plain | + f3 | text | | | | extended | +View definition: + SELECT tt1.f1, + tt1.f2, + tt1.f3 + FROM tt1 + WHERE (EXISTS ( SELECT 1 + FROM tx1 a2 + WHERE tt1.f1 = a2.x1)); + +\d+ aliased_view_4 + View "testviewschm2.aliased_view_4" + Column | Type | Collation | Nullable | Default | Storage | Description +--------+---------+-----------+----------+---------+----------+------------- + y1 | integer | | | | plain | + f2 | integer | | | | plain | + f3 | text | | | | extended | +View definition: + SELECT tt1.y1, + tt1.f2, + tt1.f3 + FROM temp_view_test.tt1 + WHERE (EXISTS ( SELECT 1 + FROM tt1 tt1_1 + WHERE tt1.y1 = tt1_1.f1)); + +ALTER TABLE tx1 RENAME TO a1; +\d+ aliased_view_1 + View "testviewschm2.aliased_view_1" + Column | Type | Collation | Nullable | Default | Storage | Description +--------+---------+-----------+----------+---------+----------+------------- + f1 | integer | | | | plain | + f2 | integer | | | | plain | + f3 | text | | | | extended | +View definition: + SELECT tt1.f1, + tt1.f2, + tt1.f3 + FROM tt1 + WHERE (EXISTS ( SELECT 1 + FROM a1 + WHERE tt1.f1 = a1.x1)); + +\d+ aliased_view_2 + View "testviewschm2.aliased_view_2" + Column | Type | Collation | Nullable | Default | Storage | Description +--------+---------+-----------+----------+---------+----------+------------- + f1 | integer | | | | plain | + f2 | integer | | | | plain | + f3 | text | | | | extended | +View definition: + SELECT a1.f1, + a1.f2, + a1.f3 + FROM tt1 a1 + WHERE (EXISTS ( SELECT 1 + FROM a1 a1_1 + WHERE a1.f1 = a1_1.x1)); + +\d+ aliased_view_3 + View "testviewschm2.aliased_view_3" + Column | Type | Collation | Nullable | Default | Storage | Description +--------+---------+-----------+----------+---------+----------+------------- + f1 | integer | | | | plain | + f2 | integer | | | | plain | + f3 | text | | | | extended | +View definition: + SELECT tt1.f1, + tt1.f2, + tt1.f3 + FROM tt1 + WHERE (EXISTS ( SELECT 1 + FROM a1 a2 + WHERE tt1.f1 = a2.x1)); + +\d+ aliased_view_4 + View "testviewschm2.aliased_view_4" + Column | Type | Collation | Nullable | Default | Storage | Description +--------+---------+-----------+----------+---------+----------+------------- + y1 | integer | | | | plain | + f2 | integer | | | | plain | + f3 | text | | | | extended | +View definition: + SELECT tt1.y1, + tt1.f2, + tt1.f3 + FROM temp_view_test.tt1 + WHERE (EXISTS ( SELECT 1 + FROM tt1 tt1_1 + WHERE tt1.y1 = tt1_1.f1)); + +ALTER TABLE tt1 RENAME TO a2; +\d+ aliased_view_1 + View "testviewschm2.aliased_view_1" + Column | Type | Collation | Nullable | Default | Storage | Description +--------+---------+-----------+----------+---------+----------+------------- + f1 | integer | | | | plain | + f2 | integer | | | | plain | + f3 | text | | | | extended | +View definition: + SELECT a2.f1, + a2.f2, + a2.f3 + FROM a2 + WHERE (EXISTS ( SELECT 1 + FROM a1 + WHERE a2.f1 = a1.x1)); + +\d+ aliased_view_2 + View "testviewschm2.aliased_view_2" + Column | Type | Collation | Nullable | Default | Storage | Description +--------+---------+-----------+----------+---------+----------+------------- + f1 | integer | | | | plain | + f2 | integer | | | | plain | + f3 | text | | | | extended | +View definition: + SELECT a1.f1, + a1.f2, + a1.f3 + FROM a2 a1 + WHERE (EXISTS ( SELECT 1 + FROM a1 a1_1 + WHERE a1.f1 = a1_1.x1)); + +\d+ aliased_view_3 + View "testviewschm2.aliased_view_3" + Column | Type | Collation | Nullable | Default | Storage | Description +--------+---------+-----------+----------+---------+----------+------------- + f1 | integer | | | | plain | + f2 | integer | | | | plain | + f3 | text | | | | extended | +View definition: + SELECT a2.f1, + a2.f2, + a2.f3 + FROM a2 + WHERE (EXISTS ( SELECT 1 + FROM a1 a2_1 + WHERE a2.f1 = a2_1.x1)); + +\d+ aliased_view_4 + View "testviewschm2.aliased_view_4" + Column | Type | Collation | Nullable | Default | Storage | Description +--------+---------+-----------+----------+---------+----------+------------- + y1 | integer | | | | plain | + f2 | integer | | | | plain | + f3 | text | | | | extended | +View definition: + SELECT tt1.y1, + tt1.f2, + tt1.f3 + FROM temp_view_test.tt1 + WHERE (EXISTS ( SELECT 1 + FROM a2 + WHERE tt1.y1 = a2.f1)); + +ALTER TABLE a1 RENAME TO tt1; +\d+ aliased_view_1 + View "testviewschm2.aliased_view_1" + Column | Type | Collation | Nullable | Default | Storage | Description +--------+---------+-----------+----------+---------+----------+------------- + f1 | integer | | | | plain | + f2 | integer | | | | plain | + f3 | text | | | | extended | +View definition: + SELECT a2.f1, + a2.f2, + a2.f3 + FROM a2 + WHERE (EXISTS ( SELECT 1 + FROM tt1 + WHERE a2.f1 = tt1.x1)); + +\d+ aliased_view_2 + View "testviewschm2.aliased_view_2" + Column | Type | Collation | Nullable | Default | Storage | Description +--------+---------+-----------+----------+---------+----------+------------- + f1 | integer | | | | plain | + f2 | integer | | | | plain | + f3 | text | | | | extended | +View definition: + SELECT a1.f1, + a1.f2, + a1.f3 + FROM a2 a1 + WHERE (EXISTS ( SELECT 1 + FROM tt1 + WHERE a1.f1 = tt1.x1)); + +\d+ aliased_view_3 + View "testviewschm2.aliased_view_3" + Column | Type | Collation | Nullable | Default | Storage | Description +--------+---------+-----------+----------+---------+----------+------------- + f1 | integer | | | | plain | + f2 | integer | | | | plain | + f3 | text | | | | extended | +View definition: + SELECT a2.f1, + a2.f2, + a2.f3 + FROM a2 + WHERE (EXISTS ( SELECT 1 + FROM tt1 a2_1 + WHERE a2.f1 = a2_1.x1)); + +\d+ aliased_view_4 + View "testviewschm2.aliased_view_4" + Column | Type | Collation | Nullable | Default | Storage | Description +--------+---------+-----------+----------+---------+----------+------------- + y1 | integer | | | | plain | + f2 | integer | | | | plain | + f3 | text | | | | extended | +View definition: + SELECT tt1.y1, + tt1.f2, + tt1.f3 + FROM temp_view_test.tt1 + WHERE (EXISTS ( SELECT 1 + FROM a2 + WHERE tt1.y1 = a2.f1)); + +ALTER TABLE a2 RENAME TO tx1; +ALTER TABLE tx1 SET SCHEMA temp_view_test; +\d+ aliased_view_1 + View "testviewschm2.aliased_view_1" + Column | Type | Collation | Nullable | Default | Storage | Description +--------+---------+-----------+----------+---------+----------+------------- + f1 | integer | | | | plain | + f2 | integer | | | | plain | + f3 | text | | | | extended | +View definition: + SELECT tx1.f1, + tx1.f2, + tx1.f3 + FROM temp_view_test.tx1 + WHERE (EXISTS ( SELECT 1 + FROM tt1 + WHERE tx1.f1 = tt1.x1)); + +\d+ aliased_view_2 + View "testviewschm2.aliased_view_2" + Column | Type | Collation | Nullable | Default | Storage | Description +--------+---------+-----------+----------+---------+----------+------------- + f1 | integer | | | | plain | + f2 | integer | | | | plain | + f3 | text | | | | extended | +View definition: + SELECT a1.f1, + a1.f2, + a1.f3 + FROM temp_view_test.tx1 a1 + WHERE (EXISTS ( SELECT 1 + FROM tt1 + WHERE a1.f1 = tt1.x1)); + +\d+ aliased_view_3 + View "testviewschm2.aliased_view_3" + Column | Type | Collation | Nullable | Default | Storage | Description +--------+---------+-----------+----------+---------+----------+------------- + f1 | integer | | | | plain | + f2 | integer | | | | plain | + f3 | text | | | | extended | +View definition: + SELECT tx1.f1, + tx1.f2, + tx1.f3 + FROM temp_view_test.tx1 + WHERE (EXISTS ( SELECT 1 + FROM tt1 a2 + WHERE tx1.f1 = a2.x1)); + +\d+ aliased_view_4 + View "testviewschm2.aliased_view_4" + Column | Type | Collation | Nullable | Default | Storage | Description +--------+---------+-----------+----------+---------+----------+------------- + y1 | integer | | | | plain | + f2 | integer | | | | plain | + f3 | text | | | | extended | +View definition: + SELECT tt1.y1, + tt1.f2, + tt1.f3 + FROM temp_view_test.tt1 + WHERE (EXISTS ( SELECT 1 + FROM temp_view_test.tx1 + WHERE tt1.y1 = tx1.f1)); + +ALTER TABLE temp_view_test.tt1 RENAME TO tmp1; +ALTER TABLE temp_view_test.tmp1 SET SCHEMA testviewschm2; +ALTER TABLE tmp1 RENAME TO tx1; +\d+ aliased_view_1 + View "testviewschm2.aliased_view_1" + Column | Type | Collation | Nullable | Default | Storage | Description +--------+---------+-----------+----------+---------+----------+------------- + f1 | integer | | | | plain | + f2 | integer | | | | plain | + f3 | text | | | | extended | +View definition: + SELECT tx1.f1, + tx1.f2, + tx1.f3 + FROM temp_view_test.tx1 + WHERE (EXISTS ( SELECT 1 + FROM tt1 + WHERE tx1.f1 = tt1.x1)); + +\d+ aliased_view_2 + View "testviewschm2.aliased_view_2" + Column | Type | Collation | Nullable | Default | Storage | Description +--------+---------+-----------+----------+---------+----------+------------- + f1 | integer | | | | plain | + f2 | integer | | | | plain | + f3 | text | | | | extended | +View definition: + SELECT a1.f1, + a1.f2, + a1.f3 + FROM temp_view_test.tx1 a1 + WHERE (EXISTS ( SELECT 1 + FROM tt1 + WHERE a1.f1 = tt1.x1)); + +\d+ aliased_view_3 + View "testviewschm2.aliased_view_3" + Column | Type | Collation | Nullable | Default | Storage | Description +--------+---------+-----------+----------+---------+----------+------------- + f1 | integer | | | | plain | + f2 | integer | | | | plain | + f3 | text | | | | extended | +View definition: + SELECT tx1.f1, + tx1.f2, + tx1.f3 + FROM temp_view_test.tx1 + WHERE (EXISTS ( SELECT 1 + FROM tt1 a2 + WHERE tx1.f1 = a2.x1)); + +\d+ aliased_view_4 + View "testviewschm2.aliased_view_4" + Column | Type | Collation | Nullable | Default | Storage | Description +--------+---------+-----------+----------+---------+----------+------------- + y1 | integer | | | | plain | + f2 | integer | | | | plain | + f3 | text | | | | extended | +View definition: + SELECT tx1.y1, + tx1.f2, + tx1.f3 + FROM tx1 + WHERE (EXISTS ( SELECT 1 + FROM temp_view_test.tx1 tx1_1 + WHERE tx1.y1 = tx1_1.f1)); + +-- Test aliasing of joins +create view view_of_joins as +select * from + (select * from (tbl1 cross join tbl2) same) ss, + (tbl3 cross join tbl4) same; +\d+ view_of_joins + View "testviewschm2.view_of_joins" + Column | Type | Collation | Nullable | Default | Storage | Description +--------+---------+-----------+----------+---------+---------+------------- + a | integer | | | | plain | + b | integer | | | | plain | + c | integer | | | | plain | + d | integer | | | | plain | + e | integer | | | | plain | + f | integer | | | | plain | + g | integer | | | | plain | + h | integer | | | | plain | +View definition: + SELECT ss.a, + ss.b, + ss.c, + ss.d, + same.e, + same.f, + same.g, + same.h + FROM ( SELECT same_1.a, + same_1.b, + same_1.c, + same_1.d + FROM (tbl1 + CROSS JOIN tbl2) same_1) ss, + (tbl3 + CROSS JOIN tbl4) same; + +create table tbl1a (a int, c int); +create view view_of_joins_2a as select * from tbl1 join tbl1a using (a); +create view view_of_joins_2b as select * from tbl1 join tbl1a using (a) as x; +create view view_of_joins_2c as select * from (tbl1 join tbl1a using (a)) as y; +create view view_of_joins_2d as select * from (tbl1 join tbl1a using (a) as x) as y; +select pg_get_viewdef('view_of_joins_2a', true); + pg_get_viewdef +---------------------------- + SELECT tbl1.a, + + tbl1.b, + + tbl1a.c + + FROM tbl1 + + JOIN tbl1a USING (a); +(1 row) + +select pg_get_viewdef('view_of_joins_2b', true); + pg_get_viewdef +--------------------------------- + SELECT tbl1.a, + + tbl1.b, + + tbl1a.c + + FROM tbl1 + + JOIN tbl1a USING (a) AS x; +(1 row) + +select pg_get_viewdef('view_of_joins_2c', true); + pg_get_viewdef +------------------------------- + SELECT y.a, + + y.b, + + y.c + + FROM (tbl1 + + JOIN tbl1a USING (a)) y; +(1 row) + +select pg_get_viewdef('view_of_joins_2d', true); + pg_get_viewdef +------------------------------------ + SELECT y.a, + + y.b, + + y.c + + FROM (tbl1 + + JOIN tbl1a USING (a) AS x) y; +(1 row) + +-- Test view decompilation in the face of column addition/deletion/renaming +create table tt2 (a int, b int, c int); +create table tt3 (ax int8, b int2, c numeric); +create table tt4 (ay int, b int, q int); +create view v1 as select * from tt2 natural join tt3; +create view v1a as select * from (tt2 natural join tt3) j; +create view v2 as select * from tt2 join tt3 using (b,c) join tt4 using (b); +create view v2a as select * from (tt2 join tt3 using (b,c) join tt4 using (b)) j; +create view v3 as select * from tt2 join tt3 using (b,c) full join tt4 using (b); +select pg_get_viewdef('v1', true); + pg_get_viewdef +----------------------------- + SELECT tt2.b, + + tt3.c, + + tt2.a, + + tt3.ax + + FROM tt2 + + JOIN tt3 USING (b, c); +(1 row) + +select pg_get_viewdef('v1a', true); + pg_get_viewdef +-------------------------------- + SELECT j.b, + + j.c, + + j.a, + + j.ax + + FROM (tt2 + + JOIN tt3 USING (b, c)) j; +(1 row) + +select pg_get_viewdef('v2', true); + pg_get_viewdef +---------------------------- + SELECT tt2.b, + + tt3.c, + + tt2.a, + + tt3.ax, + + tt4.ay, + + tt4.q + + FROM tt2 + + JOIN tt3 USING (b, c)+ + JOIN tt4 USING (b); +(1 row) + +select pg_get_viewdef('v2a', true); + pg_get_viewdef +----------------------------- + SELECT j.b, + + j.c, + + j.a, + + j.ax, + + j.ay, + + j.q + + FROM (tt2 + + JOIN tt3 USING (b, c) + + JOIN tt4 USING (b)) j; +(1 row) + +select pg_get_viewdef('v3', true); + pg_get_viewdef +------------------------------- + SELECT b, + + tt3.c, + + tt2.a, + + tt3.ax, + + tt4.ay, + + tt4.q + + FROM tt2 + + JOIN tt3 USING (b, c) + + FULL JOIN tt4 USING (b); +(1 row) + +alter table tt2 add column d int; +alter table tt2 add column e int; +select pg_get_viewdef('v1', true); + pg_get_viewdef +----------------------------- + SELECT tt2.b, + + tt3.c, + + tt2.a, + + tt3.ax + + FROM tt2 + + JOIN tt3 USING (b, c); +(1 row) + +select pg_get_viewdef('v1a', true); + pg_get_viewdef +-------------------------------- + SELECT j.b, + + j.c, + + j.a, + + j.ax + + FROM (tt2 + + JOIN tt3 USING (b, c)) j; +(1 row) + +select pg_get_viewdef('v2', true); + pg_get_viewdef +---------------------------- + SELECT tt2.b, + + tt3.c, + + tt2.a, + + tt3.ax, + + tt4.ay, + + tt4.q + + FROM tt2 + + JOIN tt3 USING (b, c)+ + JOIN tt4 USING (b); +(1 row) + +select pg_get_viewdef('v2a', true); + pg_get_viewdef +----------------------------- + SELECT j.b, + + j.c, + + j.a, + + j.ax, + + j.ay, + + j.q + + FROM (tt2 + + JOIN tt3 USING (b, c) + + JOIN tt4 USING (b)) j; +(1 row) + +select pg_get_viewdef('v3', true); + pg_get_viewdef +------------------------------- + SELECT b, + + tt3.c, + + tt2.a, + + tt3.ax, + + tt4.ay, + + tt4.q + + FROM tt2 + + JOIN tt3 USING (b, c) + + FULL JOIN tt4 USING (b); +(1 row) + +alter table tt3 rename c to d; +select pg_get_viewdef('v1', true); + pg_get_viewdef +------------------------------------------- + SELECT tt2.b, + + tt3.c, + + tt2.a, + + tt3.ax + + FROM tt2 + + JOIN tt3 tt3(ax, b, c) USING (b, c); +(1 row) + +select pg_get_viewdef('v1a', true); + pg_get_viewdef +---------------------------------------------- + SELECT j.b, + + j.c, + + j.a, + + j.ax + + FROM (tt2 + + JOIN tt3 tt3(ax, b, c) USING (b, c)) j; +(1 row) + +select pg_get_viewdef('v2', true); + pg_get_viewdef +------------------------------------------ + SELECT tt2.b, + + tt3.c, + + tt2.a, + + tt3.ax, + + tt4.ay, + + tt4.q + + FROM tt2 + + JOIN tt3 tt3(ax, b, c) USING (b, c)+ + JOIN tt4 USING (b); +(1 row) + +select pg_get_viewdef('v2a', true); + pg_get_viewdef +------------------------------------------ + SELECT j.b, + + j.c, + + j.a, + + j.ax, + + j.ay, + + j.q + + FROM (tt2 + + JOIN tt3 tt3(ax, b, c) USING (b, c)+ + JOIN tt4 USING (b)) j; +(1 row) + +select pg_get_viewdef('v3', true); + pg_get_viewdef +------------------------------------------ + SELECT b, + + tt3.c, + + tt2.a, + + tt3.ax, + + tt4.ay, + + tt4.q + + FROM tt2 + + JOIN tt3 tt3(ax, b, c) USING (b, c)+ + FULL JOIN tt4 USING (b); +(1 row) + +alter table tt3 add column c int; +alter table tt3 add column e int; +select pg_get_viewdef('v1', true); + pg_get_viewdef +--------------------------------------------------- + SELECT tt2.b, + + tt3.c, + + tt2.a, + + tt3.ax + + FROM tt2 + + JOIN tt3 tt3(ax, b, c, c_1, e) USING (b, c); +(1 row) + +select pg_get_viewdef('v1a', true); + pg_get_viewdef +----------------------------------------------------------------------------------- + SELECT j.b, + + j.c, + + j.a, + + j.ax + + FROM (tt2 + + JOIN tt3 tt3(ax, b, c, c_1, e) USING (b, c)) j(b, c, a, d, e, ax, c_1, e_1); +(1 row) + +select pg_get_viewdef('v2', true); + pg_get_viewdef +-------------------------------------------------- + SELECT tt2.b, + + tt3.c, + + tt2.a, + + tt3.ax, + + tt4.ay, + + tt4.q + + FROM tt2 + + JOIN tt3 tt3(ax, b, c, c_1, e) USING (b, c)+ + JOIN tt4 USING (b); +(1 row) + +select pg_get_viewdef('v2a', true); + pg_get_viewdef +----------------------------------------------------------------- + SELECT j.b, + + j.c, + + j.a, + + j.ax, + + j.ay, + + j.q + + FROM (tt2 + + JOIN tt3 tt3(ax, b, c, c_1, e) USING (b, c) + + JOIN tt4 USING (b)) j(b, c, a, d, e, ax, c_1, e_1, ay, q); +(1 row) + +select pg_get_viewdef('v3', true); + pg_get_viewdef +-------------------------------------------------- + SELECT b, + + tt3.c, + + tt2.a, + + tt3.ax, + + tt4.ay, + + tt4.q + + FROM tt2 + + JOIN tt3 tt3(ax, b, c, c_1, e) USING (b, c)+ + FULL JOIN tt4 USING (b); +(1 row) + +alter table tt2 drop column d; +select pg_get_viewdef('v1', true); + pg_get_viewdef +--------------------------------------------------- + SELECT tt2.b, + + tt3.c, + + tt2.a, + + tt3.ax + + FROM tt2 + + JOIN tt3 tt3(ax, b, c, c_1, e) USING (b, c); +(1 row) + +select pg_get_viewdef('v1a', true); + pg_get_viewdef +-------------------------------------------------------------------------------- + SELECT j.b, + + j.c, + + j.a, + + j.ax + + FROM (tt2 + + JOIN tt3 tt3(ax, b, c, c_1, e) USING (b, c)) j(b, c, a, e, ax, c_1, e_1); +(1 row) + +select pg_get_viewdef('v2', true); + pg_get_viewdef +-------------------------------------------------- + SELECT tt2.b, + + tt3.c, + + tt2.a, + + tt3.ax, + + tt4.ay, + + tt4.q + + FROM tt2 + + JOIN tt3 tt3(ax, b, c, c_1, e) USING (b, c)+ + JOIN tt4 USING (b); +(1 row) + +select pg_get_viewdef('v2a', true); + pg_get_viewdef +-------------------------------------------------------------- + SELECT j.b, + + j.c, + + j.a, + + j.ax, + + j.ay, + + j.q + + FROM (tt2 + + JOIN tt3 tt3(ax, b, c, c_1, e) USING (b, c) + + JOIN tt4 USING (b)) j(b, c, a, e, ax, c_1, e_1, ay, q); +(1 row) + +select pg_get_viewdef('v3', true); + pg_get_viewdef +-------------------------------------------------- + SELECT b, + + tt3.c, + + tt2.a, + + tt3.ax, + + tt4.ay, + + tt4.q + + FROM tt2 + + JOIN tt3 tt3(ax, b, c, c_1, e) USING (b, c)+ + FULL JOIN tt4 USING (b); +(1 row) + +create table tt5 (a int, b int); +create table tt6 (c int, d int); +create view vv1 as select * from (tt5 cross join tt6) j(aa,bb,cc,dd); +select pg_get_viewdef('vv1', true); + pg_get_viewdef +----------------------------------------- + SELECT j.aa, + + j.bb, + + j.cc, + + j.dd + + FROM (tt5 + + CROSS JOIN tt6) j(aa, bb, cc, dd); +(1 row) + +alter table tt5 add column c int; +select pg_get_viewdef('vv1', true); + pg_get_viewdef +-------------------------------------------- + SELECT j.aa, + + j.bb, + + j.cc, + + j.dd + + FROM (tt5 + + CROSS JOIN tt6) j(aa, bb, c, cc, dd); +(1 row) + +alter table tt5 add column cc int; +select pg_get_viewdef('vv1', true); + pg_get_viewdef +-------------------------------------------------- + SELECT j.aa, + + j.bb, + + j.cc, + + j.dd + + FROM (tt5 + + CROSS JOIN tt6) j(aa, bb, c, cc_1, cc, dd); +(1 row) + +alter table tt5 drop column c; +select pg_get_viewdef('vv1', true); + pg_get_viewdef +----------------------------------------------- + SELECT j.aa, + + j.bb, + + j.cc, + + j.dd + + FROM (tt5 + + CROSS JOIN tt6) j(aa, bb, cc_1, cc, dd); +(1 row) + +create view v4 as select * from v1; +alter view v1 rename column a to x; +select pg_get_viewdef('v1', true); + pg_get_viewdef +--------------------------------------------------- + SELECT tt2.b, + + tt3.c, + + tt2.a AS x, + + tt3.ax + + FROM tt2 + + JOIN tt3 tt3(ax, b, c, c_1, e) USING (b, c); +(1 row) + +select pg_get_viewdef('v4', true); + pg_get_viewdef +---------------- + SELECT v1.b, + + v1.c, + + v1.x AS a,+ + v1.ax + + FROM v1; +(1 row) + +-- Unnamed FULL JOIN USING is lots of fun too +create table tt7 (x int, xx int, y int); +alter table tt7 drop column xx; +create table tt8 (x int, z int); +create view vv2 as +select * from (values(1,2,3,4,5)) v(a,b,c,d,e) +union all +select * from tt7 full join tt8 using (x), tt8 tt8x; +select pg_get_viewdef('vv2', true); + pg_get_viewdef +------------------------------------------------ + SELECT v.a, + + v.b, + + v.c, + + v.d, + + v.e + + FROM ( VALUES (1,2,3,4,5)) v(a, b, c, d, e)+ + UNION ALL + + SELECT x AS a, + + tt7.y AS b, + + tt8.z AS c, + + tt8x.x_1 AS d, + + tt8x.z AS e + + FROM tt7 + + FULL JOIN tt8 USING (x), + + tt8 tt8x(x_1, z); +(1 row) + +create view vv3 as +select * from (values(1,2,3,4,5,6)) v(a,b,c,x,e,f) +union all +select * from + tt7 full join tt8 using (x), + tt7 tt7x full join tt8 tt8x using (x); +select pg_get_viewdef('vv3', true); + pg_get_viewdef +----------------------------------------------------- + SELECT v.a, + + v.b, + + v.c, + + v.x, + + v.e, + + v.f + + FROM ( VALUES (1,2,3,4,5,6)) v(a, b, c, x, e, f)+ + UNION ALL + + SELECT x AS a, + + tt7.y AS b, + + tt8.z AS c, + + x_1 AS x, + + tt7x.y AS e, + + tt8x.z AS f + + FROM tt7 + + FULL JOIN tt8 USING (x), + + tt7 tt7x(x_1, y) + + FULL JOIN tt8 tt8x(x_1, z) USING (x_1); +(1 row) + +create view vv4 as +select * from (values(1,2,3,4,5,6,7)) v(a,b,c,x,e,f,g) +union all +select * from + tt7 full join tt8 using (x), + tt7 tt7x full join tt8 tt8x using (x) full join tt8 tt8y using (x); +select pg_get_viewdef('vv4', true); + pg_get_viewdef +---------------------------------------------------------- + SELECT v.a, + + v.b, + + v.c, + + v.x, + + v.e, + + v.f, + + v.g + + FROM ( VALUES (1,2,3,4,5,6,7)) v(a, b, c, x, e, f, g)+ + UNION ALL + + SELECT x AS a, + + tt7.y AS b, + + tt8.z AS c, + + x_1 AS x, + + tt7x.y AS e, + + tt8x.z AS f, + + tt8y.z AS g + + FROM tt7 + + FULL JOIN tt8 USING (x), + + tt7 tt7x(x_1, y) + + FULL JOIN tt8 tt8x(x_1, z) USING (x_1) + + FULL JOIN tt8 tt8y(x_1, z) USING (x_1); +(1 row) + +alter table tt7 add column zz int; +alter table tt7 add column z int; +alter table tt7 drop column zz; +alter table tt8 add column z2 int; +select pg_get_viewdef('vv2', true); + pg_get_viewdef +------------------------------------------------ + SELECT v.a, + + v.b, + + v.c, + + v.d, + + v.e + + FROM ( VALUES (1,2,3,4,5)) v(a, b, c, d, e)+ + UNION ALL + + SELECT x AS a, + + tt7.y AS b, + + tt8.z AS c, + + tt8x.x_1 AS d, + + tt8x.z AS e + + FROM tt7 + + FULL JOIN tt8 USING (x), + + tt8 tt8x(x_1, z, z2); +(1 row) + +select pg_get_viewdef('vv3', true); + pg_get_viewdef +----------------------------------------------------- + SELECT v.a, + + v.b, + + v.c, + + v.x, + + v.e, + + v.f + + FROM ( VALUES (1,2,3,4,5,6)) v(a, b, c, x, e, f)+ + UNION ALL + + SELECT x AS a, + + tt7.y AS b, + + tt8.z AS c, + + x_1 AS x, + + tt7x.y AS e, + + tt8x.z AS f + + FROM tt7 + + FULL JOIN tt8 USING (x), + + tt7 tt7x(x_1, y, z) + + FULL JOIN tt8 tt8x(x_1, z, z2) USING (x_1); +(1 row) + +select pg_get_viewdef('vv4', true); + pg_get_viewdef +---------------------------------------------------------- + SELECT v.a, + + v.b, + + v.c, + + v.x, + + v.e, + + v.f, + + v.g + + FROM ( VALUES (1,2,3,4,5,6,7)) v(a, b, c, x, e, f, g)+ + UNION ALL + + SELECT x AS a, + + tt7.y AS b, + + tt8.z AS c, + + x_1 AS x, + + tt7x.y AS e, + + tt8x.z AS f, + + tt8y.z AS g + + FROM tt7 + + FULL JOIN tt8 USING (x), + + tt7 tt7x(x_1, y, z) + + FULL JOIN tt8 tt8x(x_1, z, z2) USING (x_1) + + FULL JOIN tt8 tt8y(x_1, z, z2) USING (x_1); +(1 row) + +-- Implicit coercions in a JOIN USING create issues similar to FULL JOIN +create table tt7a (x date, xx int, y int); +alter table tt7a drop column xx; +create table tt8a (x timestamptz, z int); +create view vv2a as +select * from (values(now(),2,3,now(),5)) v(a,b,c,d,e) +union all +select * from tt7a left join tt8a using (x), tt8a tt8ax; +select pg_get_viewdef('vv2a', true); + pg_get_viewdef +-------------------------------------------------------- + SELECT v.a, + + v.b, + + v.c, + + v.d, + + v.e + + FROM ( VALUES (now(),2,3,now(),5)) v(a, b, c, d, e)+ + UNION ALL + + SELECT x AS a, + + tt7a.y AS b, + + tt8a.z AS c, + + tt8ax.x_1 AS d, + + tt8ax.z AS e + + FROM tt7a + + LEFT JOIN tt8a USING (x), + + tt8a tt8ax(x_1, z); +(1 row) + +-- +-- Also check dropping a column that existed when the view was made +-- +create table tt9 (x int, xx int, y int); +create table tt10 (x int, z int); +create view vv5 as select x,y,z from tt9 join tt10 using(x); +select pg_get_viewdef('vv5', true); + pg_get_viewdef +--------------------------- + SELECT tt9.x, + + tt9.y, + + tt10.z + + FROM tt9 + + JOIN tt10 USING (x); +(1 row) + +alter table tt9 drop column xx; +select pg_get_viewdef('vv5', true); + pg_get_viewdef +--------------------------- + SELECT tt9.x, + + tt9.y, + + tt10.z + + FROM tt9 + + JOIN tt10 USING (x); +(1 row) + +-- +-- Another corner case is that we might add a column to a table below a +-- JOIN USING, and thereby make the USING column name ambiguous +-- +create table tt11 (x int, y int); +create table tt12 (x int, z int); +create table tt13 (z int, q int); +create view vv6 as select x,y,z,q from + (tt11 join tt12 using(x)) join tt13 using(z); +select pg_get_viewdef('vv6', true); + pg_get_viewdef +--------------------------- + SELECT tt11.x, + + tt11.y, + + tt12.z, + + tt13.q + + FROM tt11 + + JOIN tt12 USING (x) + + JOIN tt13 USING (z); +(1 row) + +alter table tt11 add column z int; +select pg_get_viewdef('vv6', true); + pg_get_viewdef +------------------------------ + SELECT tt11.x, + + tt11.y, + + tt12.z, + + tt13.q + + FROM tt11 tt11(x, y, z_1)+ + JOIN tt12 USING (x) + + JOIN tt13 USING (z); +(1 row) + +-- +-- Check cases involving dropped/altered columns in a function's rowtype result +-- +create table tt14t (f1 text, f2 text, f3 text, f4 text); +insert into tt14t values('foo', 'bar', 'baz', '42'); +alter table tt14t drop column f2; +create function tt14f() returns setof tt14t as +$$ +declare + rec1 record; +begin + for rec1 in select * from tt14t + loop + return next rec1; + end loop; +end; +$$ +language plpgsql; +create view tt14v as select t.* from tt14f() t; +select pg_get_viewdef('tt14v', true); + pg_get_viewdef +-------------------------------- + SELECT t.f1, + + t.f3, + + t.f4 + + FROM tt14f() t(f1, f3, f4); +(1 row) + +select * from tt14v; + f1 | f3 | f4 +-----+-----+---- + foo | baz | 42 +(1 row) + +alter table tt14t drop column f3; -- fail, view has explicit reference to f3 +ERROR: cannot drop column f3 of table tt14t because other objects depend on it +DETAIL: view tt14v depends on column f3 of table tt14t +HINT: Use DROP ... CASCADE to drop the dependent objects too. +-- We used to have a bug that would allow the above to succeed, posing +-- hazards for later execution of the view. Check that the internal +-- defenses for those hazards haven't bit-rotted, in case some other +-- bug with similar symptoms emerges. +begin; +-- destroy the dependency entry that prevents the DROP: +delete from pg_depend where + objid = (select oid from pg_rewrite + where ev_class = 'tt14v'::regclass and rulename = '_RETURN') + and refobjsubid = 3 +returning pg_describe_object(classid, objid, objsubid) as obj, + pg_describe_object(refclassid, refobjid, refobjsubid) as ref, + deptype; + obj | ref | deptype +----------------------------+--------------------------+--------- + rule _RETURN on view tt14v | column f3 of table tt14t | n +(1 row) + +-- this will now succeed: +alter table tt14t drop column f3; +-- column f3 is still in the view, sort of ... +select pg_get_viewdef('tt14v', true); + pg_get_viewdef +--------------------------------- + SELECT t.f1, + + t."?dropped?column?" AS f3,+ + t.f4 + + FROM tt14f() t(f1, f4); +(1 row) + +-- ... and you can even EXPLAIN it ... +explain (verbose, costs off) select * from tt14v; + QUERY PLAN +---------------------------------------- + Function Scan on testviewschm2.tt14f t + Output: t.f1, t.f3, t.f4 + Function Call: tt14f() +(3 rows) + +-- but it will fail at execution +select f1, f4 from tt14v; + f1 | f4 +-----+---- + foo | 42 +(1 row) + +select * from tt14v; +ERROR: attribute 3 of type record has been dropped +rollback; +-- likewise, altering a referenced column's type is prohibited ... +alter table tt14t alter column f4 type integer using f4::integer; -- fail +ERROR: cannot alter type of a column used by a view or rule +DETAIL: rule _RETURN on view tt14v depends on column "f4" +-- ... but some bug might let it happen, so check defenses +begin; +-- destroy the dependency entry that prevents the ALTER: +delete from pg_depend where + objid = (select oid from pg_rewrite + where ev_class = 'tt14v'::regclass and rulename = '_RETURN') + and refobjsubid = 4 +returning pg_describe_object(classid, objid, objsubid) as obj, + pg_describe_object(refclassid, refobjid, refobjsubid) as ref, + deptype; + obj | ref | deptype +----------------------------+--------------------------+--------- + rule _RETURN on view tt14v | column f4 of table tt14t | n +(1 row) + +-- this will now succeed: +alter table tt14t alter column f4 type integer using f4::integer; +-- f4 is still in the view ... +select pg_get_viewdef('tt14v', true); + pg_get_viewdef +-------------------------------- + SELECT t.f1, + + t.f3, + + t.f4 + + FROM tt14f() t(f1, f3, f4); +(1 row) + +-- but will fail at execution +select f1, f3 from tt14v; + f1 | f3 +-----+----- + foo | baz +(1 row) + +select * from tt14v; +ERROR: attribute 4 of type record has wrong type +DETAIL: Table has type integer, but query expects text. +rollback; +drop view tt14v; +create view tt14v as select t.f1, t.f4 from tt14f() t; +select pg_get_viewdef('tt14v', true); + pg_get_viewdef +-------------------------------- + SELECT t.f1, + + t.f4 + + FROM tt14f() t(f1, f3, f4); +(1 row) + +select * from tt14v; + f1 | f4 +-----+---- + foo | 42 +(1 row) + +alter table tt14t drop column f3; -- ok +select pg_get_viewdef('tt14v', true); + pg_get_viewdef +---------------------------- + SELECT t.f1, + + t.f4 + + FROM tt14f() t(f1, f4); +(1 row) + +explain (verbose, costs off) select * from tt14v; + QUERY PLAN +---------------------------------------- + Function Scan on testviewschm2.tt14f t + Output: t.f1, t.f4 + Function Call: tt14f() +(3 rows) + +select * from tt14v; + f1 | f4 +-----+---- + foo | 42 +(1 row) + +-- check display of whole-row variables in some corner cases +create type nestedcomposite as (x int8_tbl); +create view tt15v as select row(i)::nestedcomposite from int8_tbl i; +select * from tt15v; + row +------------------------------------------ + ("(123,456)") + ("(123,4567890123456789)") + ("(4567890123456789,123)") + ("(4567890123456789,4567890123456789)") + ("(4567890123456789,-4567890123456789)") +(5 rows) + +select pg_get_viewdef('tt15v', true); + pg_get_viewdef +------------------------------------------------------ + SELECT ROW(i.*::int8_tbl)::nestedcomposite AS "row"+ + FROM int8_tbl i; +(1 row) + +select row(i.*::int8_tbl)::nestedcomposite from int8_tbl i; + row +------------------------------------------ + ("(123,456)") + ("(123,4567890123456789)") + ("(4567890123456789,123)") + ("(4567890123456789,4567890123456789)") + ("(4567890123456789,-4567890123456789)") +(5 rows) + +create view tt16v as select * from int8_tbl i, lateral(values(i)) ss; +select * from tt16v; + q1 | q2 | column1 +------------------+-------------------+-------------------------------------- + 123 | 456 | (123,456) + 123 | 4567890123456789 | (123,4567890123456789) + 4567890123456789 | 123 | (4567890123456789,123) + 4567890123456789 | 4567890123456789 | (4567890123456789,4567890123456789) + 4567890123456789 | -4567890123456789 | (4567890123456789,-4567890123456789) +(5 rows) + +select pg_get_viewdef('tt16v', true); + pg_get_viewdef +------------------------------------------- + SELECT i.q1, + + i.q2, + + ss.column1 + + FROM int8_tbl i, + + LATERAL ( VALUES (i.*::int8_tbl)) ss; +(1 row) + +select * from int8_tbl i, lateral(values(i.*::int8_tbl)) ss; + q1 | q2 | column1 +------------------+-------------------+-------------------------------------- + 123 | 456 | (123,456) + 123 | 4567890123456789 | (123,4567890123456789) + 4567890123456789 | 123 | (4567890123456789,123) + 4567890123456789 | 4567890123456789 | (4567890123456789,4567890123456789) + 4567890123456789 | -4567890123456789 | (4567890123456789,-4567890123456789) +(5 rows) + +create view tt17v as select * from int8_tbl i where i in (values(i)); +select * from tt17v; + q1 | q2 +------------------+------------------- + 123 | 456 + 123 | 4567890123456789 + 4567890123456789 | 123 + 4567890123456789 | 4567890123456789 + 4567890123456789 | -4567890123456789 +(5 rows) + +select pg_get_viewdef('tt17v', true); + pg_get_viewdef +--------------------------------------------- + SELECT i.q1, + + i.q2 + + FROM int8_tbl i + + WHERE (i.* IN ( VALUES (i.*::int8_tbl))); +(1 row) + +select * from int8_tbl i where i.* in (values(i.*::int8_tbl)); + q1 | q2 +------------------+------------------- + 123 | 456 + 123 | 4567890123456789 + 4567890123456789 | 123 + 4567890123456789 | 4567890123456789 + 4567890123456789 | -4567890123456789 +(5 rows) + +create table tt15v_log(o tt15v, n tt15v, incr bool); +create rule updlog as on update to tt15v do also + insert into tt15v_log values(old, new, row(old,old) < row(new,new)); +\d+ tt15v + View "testviewschm2.tt15v" + Column | Type | Collation | Nullable | Default | Storage | Description +--------+-----------------+-----------+----------+---------+----------+------------- + row | nestedcomposite | | | | extended | +View definition: + SELECT ROW(i.*::int8_tbl)::nestedcomposite AS "row" + FROM int8_tbl i; +Rules: + updlog AS + ON UPDATE TO tt15v DO INSERT INTO tt15v_log (o, n, incr) + VALUES (old.*::tt15v, new.*::tt15v, (ROW(old.*::tt15v, old.*::tt15v) < ROW(new.*::tt15v, new.*::tt15v))) + +-- check unique-ification of overlength names +create view tt18v as + select * from int8_tbl xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxy + union all + select * from int8_tbl xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxz; +NOTICE: identifier "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxy" will be truncated to "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx" +NOTICE: identifier "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxz" will be truncated to "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx" +select pg_get_viewdef('tt18v', true); + pg_get_viewdef +----------------------------------------------------------------------------------- + SELECT xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx.q1, + + xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx.q2 + + FROM int8_tbl xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx + + UNION ALL + + SELECT xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx.q1, + + xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx.q2 + + FROM int8_tbl xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx; +(1 row) + +explain (costs off) select * from tt18v; + QUERY PLAN +-------------------------------------------------------------------------------------------- + Append + -> Seq Scan on int8_tbl xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx + -> Seq Scan on int8_tbl xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx_1 +(3 rows) + +-- check display of ScalarArrayOp with a sub-select +select 'foo'::text = any(array['abc','def','foo']::text[]); + ?column? +---------- + t +(1 row) + +select 'foo'::text = any((select array['abc','def','foo']::text[])); -- fail +ERROR: operator does not exist: text = text[] +LINE 1: select 'foo'::text = any((select array['abc','def','foo']::t... + ^ +HINT: No operator matches the given name and argument types. You might need to add explicit type casts. +select 'foo'::text = any((select array['abc','def','foo']::text[])::text[]); + ?column? +---------- + t +(1 row) + +create view tt19v as +select 'foo'::text = any(array['abc','def','foo']::text[]) c1, + 'foo'::text = any((select array['abc','def','foo']::text[])::text[]) c2; +select pg_get_viewdef('tt19v', true); + pg_get_viewdef +------------------------------------------------------------------------------------------------------------ + SELECT 'foo'::text = ANY (ARRAY['abc'::text, 'def'::text, 'foo'::text]) AS c1, + + 'foo'::text = ANY ((( SELECT ARRAY['abc'::text, 'def'::text, 'foo'::text] AS "array"))::text[]) AS c2; +(1 row) + +-- check display of assorted RTE_FUNCTION expressions +create view tt20v as +select * from + coalesce(1,2) as c, + collation for ('x'::text) col, + current_date as d, + localtimestamp(3) as t, + cast(1+2 as int4) as i4, + cast(1+2 as int8) as i8; +select pg_get_viewdef('tt20v', true); + pg_get_viewdef +--------------------------------------------- + SELECT c.c, + + col.col, + + d.d, + + t.t, + + i4.i4, + + i8.i8 + + FROM COALESCE(1, 2) c(c), + + COLLATION FOR ('x'::text) col(col), + + CURRENT_DATE d(d), + + LOCALTIMESTAMP(3) t(t), + + CAST(1 + 2 AS integer) i4(i4), + + CAST((1 + 2)::bigint AS bigint) i8(i8); +(1 row) + +-- reverse-listing of various special function syntaxes required by SQL +create view tt201v as +select + ('2022-12-01'::date + '1 day'::interval) at time zone 'UTC' as atz, + extract(day from now()) as extr, + (now(), '1 day'::interval) overlaps + (current_timestamp(2), '1 day'::interval) as o, + 'foo' is normalized isn, + 'foo' is nfkc normalized isnn, + normalize('foo') as n, + normalize('foo', nfkd) as nfkd, + overlay('foo' placing 'bar' from 2) as ovl, + overlay('foo' placing 'bar' from 2 for 3) as ovl2, + position('foo' in 'foobar') as p, + substring('foo' from 2 for 3) as s, + substring('foo' similar 'f' escape '#') as ss, + substring('foo' from 'oo') as ssf, -- historically-permitted abuse + trim(' ' from ' foo ') as bt, + trim(leading ' ' from ' foo ') as lt, + trim(trailing ' foo ') as rt, + trim(E'\\000'::bytea from E'\\000Tom\\000'::bytea) as btb, + trim(leading E'\\000'::bytea from E'\\000Tom\\000'::bytea) as ltb, + trim(trailing E'\\000'::bytea from E'\\000Tom\\000'::bytea) as rtb; +select pg_get_viewdef('tt201v', true); + pg_get_viewdef +----------------------------------------------------------------------------------------------- + SELECT (('12-01-2022'::date + '@ 1 day'::interval) AT TIME ZONE 'UTC'::text) AS atz, + + EXTRACT(day FROM now()) AS extr, + + ((now(), '@ 1 day'::interval) OVERLAPS (CURRENT_TIMESTAMP(2), '@ 1 day'::interval)) AS o,+ + ('foo'::text IS NORMALIZED) AS isn, + + ('foo'::text IS NFKC NORMALIZED) AS isnn, + + NORMALIZE('foo'::text) AS n, + + NORMALIZE('foo'::text, NFKD) AS nfkd, + + OVERLAY('foo'::text PLACING 'bar'::text FROM 2) AS ovl, + + OVERLAY('foo'::text PLACING 'bar'::text FROM 2 FOR 3) AS ovl2, + + POSITION(('foo'::text) IN ('foobar'::text)) AS p, + + SUBSTRING('foo'::text FROM 2 FOR 3) AS s, + + SUBSTRING('foo'::text SIMILAR 'f'::text ESCAPE '#'::text) AS ss, + + "substring"('foo'::text, 'oo'::text) AS ssf, + + TRIM(BOTH ' '::text FROM ' foo '::text) AS bt, + + TRIM(LEADING ' '::text FROM ' foo '::text) AS lt, + + TRIM(TRAILING FROM ' foo '::text) AS rt, + + TRIM(BOTH '\x00'::bytea FROM '\x00546f6d00'::bytea) AS btb, + + TRIM(LEADING '\x00'::bytea FROM '\x00546f6d00'::bytea) AS ltb, + + TRIM(TRAILING '\x00'::bytea FROM '\x00546f6d00'::bytea) AS rtb; +(1 row) + +-- corner cases with empty join conditions +create view tt21v as +select * from tt5 natural inner join tt6; +select pg_get_viewdef('tt21v', true); + pg_get_viewdef +---------------------- + SELECT tt5.a, + + tt5.b, + + tt5.cc, + + tt6.c, + + tt6.d + + FROM tt5 + + CROSS JOIN tt6; +(1 row) + +create view tt22v as +select * from tt5 natural left join tt6; +select pg_get_viewdef('tt22v', true); + pg_get_viewdef +----------------------------- + SELECT tt5.a, + + tt5.b, + + tt5.cc, + + tt6.c, + + tt6.d + + FROM tt5 + + LEFT JOIN tt6 ON TRUE; +(1 row) + +-- check handling of views with immediately-renamed columns +create view tt23v (col_a, col_b) as +select q1 as other_name1, q2 as other_name2 from int8_tbl +union +select 42, 43; +select pg_get_viewdef('tt23v', true); + pg_get_viewdef +------------------------------- + SELECT int8_tbl.q1 AS col_a,+ + int8_tbl.q2 AS col_b + + FROM int8_tbl + + UNION + + SELECT 42 AS col_a, + + 43 AS col_b; +(1 row) + +select pg_get_ruledef(oid, true) from pg_rewrite + where ev_class = 'tt23v'::regclass and ev_type = '1'; + pg_get_ruledef +----------------------------------------------------------------- + CREATE RULE "_RETURN" AS + + ON SELECT TO tt23v DO INSTEAD SELECT int8_tbl.q1 AS col_a,+ + int8_tbl.q2 AS col_b + + FROM int8_tbl + + UNION + + SELECT 42 AS col_a, + + 43 AS col_b; +(1 row) + +-- test extraction of FieldSelect field names (get_name_for_var_field) +create view tt24v as +with cte as materialized (select r from (values(1,2),(3,4)) r) +select (r).column2 as col_a, (rr).column2 as col_b from + cte join (select rr from (values(1,7),(3,8)) rr limit 2) ss + on (r).column1 = (rr).column1; +select pg_get_viewdef('tt24v', true); + pg_get_viewdef +------------------------------------------------------------ + WITH cte AS MATERIALIZED ( + + SELECT r.*::record AS r + + FROM ( VALUES (1,2), (3,4)) r + + ) + + SELECT (cte.r).column2 AS col_a, + + (ss.rr).column2 AS col_b + + FROM cte + + JOIN ( SELECT rr.*::record AS rr + + FROM ( VALUES (1,7), (3,8)) rr + + LIMIT 2) ss ON (cte.r).column1 = (ss.rr).column1; +(1 row) + +create view tt25v as +with cte as materialized (select pg_get_keywords() k) +select (k).word from cte; +select pg_get_viewdef('tt25v', true); + pg_get_viewdef +---------------------------------------- + WITH cte AS MATERIALIZED ( + + SELECT pg_get_keywords() AS k+ + ) + + SELECT (cte.k).word AS word + + FROM cte; +(1 row) + +-- also check cases seen only in EXPLAIN +explain (verbose, costs off) +select * from tt24v; + QUERY PLAN +------------------------------------------------------------------------------------------ + Hash Join + Output: (cte.r).column2, ((ROW("*VALUES*".column1, "*VALUES*".column2))).column2 + Hash Cond: (((ROW("*VALUES*".column1, "*VALUES*".column2))).column1 = (cte.r).column1) + CTE cte + -> Values Scan on "*VALUES*_1" + Output: ROW("*VALUES*_1".column1, "*VALUES*_1".column2) + -> Limit + Output: (ROW("*VALUES*".column1, "*VALUES*".column2)) + -> Values Scan on "*VALUES*" + Output: ROW("*VALUES*".column1, "*VALUES*".column2) + -> Hash + Output: cte.r + -> CTE Scan on cte + Output: cte.r +(14 rows) + +explain (verbose, costs off) +select (r).column2 from (select r from (values(1,2),(3,4)) r limit 1) ss; + QUERY PLAN +------------------------------------------------------------------- + Subquery Scan on ss + Output: (ss.r).column2 + -> Limit + Output: (ROW("*VALUES*".column1, "*VALUES*".column2)) + -> Values Scan on "*VALUES*" + Output: ROW("*VALUES*".column1, "*VALUES*".column2) +(6 rows) + +-- test pretty-print parenthesization rules, and SubLink deparsing +create view tt26v as +select x + y + z as c1, + (x * y) + z as c2, + x + (y * z) as c3, + (x + y) * z as c4, + x * (y + z) as c5, + x + (y + z) as c6, + x + (y # z) as c7, + (x > y) AND (y > z OR x > z) as c8, + (x > y) OR (y > z AND NOT (x > z)) as c9, + (x,y) <> ALL (values(1,2),(3,4)) as c10, + (x,y) <= ANY (values(1,2),(3,4)) as c11 +from (values(1,2,3)) v(x,y,z); +select pg_get_viewdef('tt26v', true); + pg_get_viewdef +-------------------------------------------------------- + SELECT v.x + v.y + v.z AS c1, + + v.x * v.y + v.z AS c2, + + v.x + v.y * v.z AS c3, + + (v.x + v.y) * v.z AS c4, + + v.x * (v.y + v.z) AS c5, + + v.x + (v.y + v.z) AS c6, + + v.x + (v.y # v.z) AS c7, + + v.x > v.y AND (v.y > v.z OR v.x > v.z) AS c8, + + v.x > v.y OR v.y > v.z AND NOT v.x > v.z AS c9, + + ((v.x, v.y) <> ALL ( VALUES (1,2), (3,4))) AS c10,+ + ((v.x, v.y) <= ANY ( VALUES (1,2), (3,4))) AS c11 + + FROM ( VALUES (1,2,3)) v(x, y, z); +(1 row) + +-- Test that changing the relkind of a relcache entry doesn't cause +-- trouble. Prior instances of where it did: +-- CALDaNm2yXz+zOtv7y5zBd5WKT8O0Ld3YxikuU3dcyCvxF7gypA@mail.gmail.com +-- CALDaNm3oZA-8Wbps2Jd1g5_Gjrr-x3YWrJPek-mF5Asrrvz2Dg@mail.gmail.com +CREATE TABLE tt26(c int); +BEGIN; +CREATE TABLE tt27(c int); +SAVEPOINT q; +CREATE RULE "_RETURN" AS ON SELECT TO tt27 DO INSTEAD SELECT * FROM tt26; +SELECT * FROM tt27; + c +--- +(0 rows) + +ROLLBACK TO q; +CREATE RULE "_RETURN" AS ON SELECT TO tt27 DO INSTEAD SELECT * FROM tt26; +ROLLBACK; +BEGIN; +CREATE TABLE tt28(c int); +CREATE RULE "_RETURN" AS ON SELECT TO tt28 DO INSTEAD SELECT * FROM tt26; +CREATE RULE "_RETURN" AS ON SELECT TO tt28 DO INSTEAD SELECT * FROM tt26; +ERROR: "tt28" is already a view +ROLLBACK; +-- clean up all the random objects we made above +DROP SCHEMA temp_view_test CASCADE; +NOTICE: drop cascades to 27 other objects +DETAIL: drop cascades to table temp_view_test.base_table +drop cascades to view v2_temp +drop cascades to view v4_temp +drop cascades to view v6_temp +drop cascades to view v7_temp +drop cascades to view v10_temp +drop cascades to view v8_temp +drop cascades to view v9_temp +drop cascades to view v11_temp +drop cascades to view v12_temp +drop cascades to table temp_view_test.base_table2 +drop cascades to view v5_temp +drop cascades to view temp_view_test.v1 +drop cascades to view temp_view_test.v2 +drop cascades to view temp_view_test.v3 +drop cascades to view temp_view_test.v4 +drop cascades to view temp_view_test.v5 +drop cascades to view temp_view_test.v6 +drop cascades to view temp_view_test.v7 +drop cascades to view temp_view_test.v8 +drop cascades to sequence temp_view_test.seq1 +drop cascades to view temp_view_test.v9 +drop cascades to table temp_view_test.tx1 +drop cascades to view aliased_view_1 +drop cascades to view aliased_view_2 +drop cascades to view aliased_view_3 +drop cascades to view aliased_view_4 +DROP SCHEMA testviewschm2 CASCADE; +NOTICE: drop cascades to 78 other objects +DETAIL: drop cascades to table t1 +drop cascades to view temporal1 +drop cascades to view temporal2 +drop cascades to view temporal3 +drop cascades to view temporal4 +drop cascades to table t2 +drop cascades to view nontemp1 +drop cascades to view nontemp2 +drop cascades to view nontemp3 +drop cascades to view nontemp4 +drop cascades to table tbl1 +drop cascades to table tbl2 +drop cascades to table tbl3 +drop cascades to table tbl4 +drop cascades to view mytempview +drop cascades to view pubview +drop cascades to view mysecview1 +drop cascades to view mysecview2 +drop cascades to view mysecview3 +drop cascades to view mysecview4 +drop cascades to view mysecview7 +drop cascades to view mysecview8 +drop cascades to view mysecview9 +drop cascades to view unspecified_types +drop cascades to table tt1 +drop cascades to table tx1 +drop cascades to view view_of_joins +drop cascades to table tbl1a +drop cascades to view view_of_joins_2a +drop cascades to view view_of_joins_2b +drop cascades to view view_of_joins_2c +drop cascades to view view_of_joins_2d +drop cascades to table tt2 +drop cascades to table tt3 +drop cascades to table tt4 +drop cascades to view v1 +drop cascades to view v1a +drop cascades to view v2 +drop cascades to view v2a +drop cascades to view v3 +drop cascades to table tt5 +drop cascades to table tt6 +drop cascades to view vv1 +drop cascades to view v4 +drop cascades to table tt7 +drop cascades to table tt8 +drop cascades to view vv2 +drop cascades to view vv3 +drop cascades to view vv4 +drop cascades to table tt7a +drop cascades to table tt8a +drop cascades to view vv2a +drop cascades to table tt9 +drop cascades to table tt10 +drop cascades to view vv5 +drop cascades to table tt11 +drop cascades to table tt12 +drop cascades to table tt13 +drop cascades to view vv6 +drop cascades to table tt14t +drop cascades to function tt14f() +drop cascades to view tt14v +drop cascades to type nestedcomposite +drop cascades to view tt15v +drop cascades to view tt16v +drop cascades to view tt17v +drop cascades to table tt15v_log +drop cascades to view tt18v +drop cascades to view tt19v +drop cascades to view tt20v +drop cascades to view tt201v +drop cascades to view tt21v +drop cascades to view tt22v +drop cascades to view tt23v +drop cascades to view tt24v +drop cascades to view tt25v +drop cascades to view tt26v +drop cascades to table tt26 |