aboutsummaryrefslogtreecommitdiff
path: root/src
diff options
context:
space:
mode:
Diffstat (limited to 'src')
-rw-r--r--src/test/regress/expected/generated_stored.out140
-rw-r--r--src/test/regress/sql/generated_stored.sql59
2 files changed, 179 insertions, 20 deletions
diff --git a/src/test/regress/expected/generated_stored.out b/src/test/regress/expected/generated_stored.out
index e28cc3919da..43db7442a67 100644
--- a/src/test/regress/expected/generated_stored.out
+++ b/src/test/regress/expected/generated_stored.out
@@ -122,6 +122,24 @@ SELECT * FROM gtest1 ORDER BY a;
4 | 8
(4 rows)
+SELECT gtest1 FROM gtest1 ORDER BY a; -- whole-row reference
+ gtest1
+--------
+ (1,2)
+ (2,4)
+ (3,6)
+ (4,8)
+(4 rows)
+
+SELECT a, (SELECT gtest1.b) FROM gtest1 ORDER BY a; -- sublink
+ a | b
+---+---
+ 1 | 2
+ 2 | 4
+ 3 | 6
+ 4 | 8
+(4 rows)
+
DELETE FROM gtest1 WHERE a >= 3;
UPDATE gtest1 SET b = DEFAULT WHERE a = 1;
UPDATE gtest1 SET b = 11 WHERE a = 1; -- error
@@ -177,7 +195,12 @@ SELECT * FROM gtest1 ORDER BY a;
2 | 4
(2 rows)
-UPDATE gtest1 SET a = 3 WHERE b = 4;
+UPDATE gtest1 SET a = 3 WHERE b = 4 RETURNING old.*, new.*;
+ a | b | a | b
+---+---+---+---
+ 2 | 4 | 3 | 6
+(1 row)
+
SELECT * FROM gtest1 ORDER BY a;
a | b
---+---
@@ -203,7 +226,14 @@ CREATE TABLE gtestm (
INSERT INTO gtestm VALUES (1, 5, 100);
MERGE INTO gtestm t USING (VALUES (1, 10), (2, 20)) v(id, f1) ON t.id = v.id
WHEN MATCHED THEN UPDATE SET f1 = v.f1
- WHEN NOT MATCHED THEN INSERT VALUES (v.id, v.f1, 200);
+ WHEN NOT MATCHED THEN INSERT VALUES (v.id, v.f1, 200)
+ RETURNING merge_action(), old.*, new.*;
+ merge_action | id | f1 | f2 | f3 | f4 | id | f1 | f2 | f3 | f4
+--------------+----+----+-----+----+-----+----+----+-----+----+-----
+ UPDATE | 1 | 5 | 100 | 10 | 200 | 1 | 10 | 100 | 20 | 200
+ INSERT | | | | | | 2 | 20 | 200 | 40 | 400
+(2 rows)
+
SELECT * FROM gtestm ORDER BY id;
id | f1 | f2 | f3 | f4
----+----+-----+----+-----
@@ -212,6 +242,27 @@ SELECT * FROM gtestm ORDER BY id;
(2 rows)
DROP TABLE gtestm;
+CREATE TABLE gtestm (
+ a int PRIMARY KEY,
+ b int GENERATED ALWAYS AS (a * 2) STORED
+);
+INSERT INTO gtestm (a) SELECT g FROM generate_series(1, 10) g;
+MERGE INTO gtestm t USING gtestm AS s ON 2 * t.a = s.b WHEN MATCHED THEN DELETE RETURNING *;
+ a | b | a | b
+----+----+----+----
+ 1 | 2 | 1 | 2
+ 2 | 4 | 2 | 4
+ 3 | 6 | 3 | 6
+ 4 | 8 | 4 | 8
+ 5 | 10 | 5 | 10
+ 6 | 12 | 6 | 12
+ 7 | 14 | 7 | 14
+ 8 | 16 | 8 | 16
+ 9 | 18 | 9 | 18
+ 10 | 20 | 10 | 20
+(10 rows)
+
+DROP TABLE gtestm;
-- views
CREATE VIEW gtest1v AS SELECT * FROM gtest1;
SELECT * FROM gtest1v;
@@ -319,6 +370,21 @@ Not-null constraints:
"gtest1_a_not_null" NOT NULL "a" (inherited)
Inherits: gtest1
+INSERT INTO gtestx (a, x) VALUES (11, 22);
+SELECT * FROM gtest1;
+ a | b
+----+-----
+ 3 | 6
+ 4 | 8
+ 11 | 242
+(3 rows)
+
+SELECT * FROM gtestx;
+ a | b | x
+----+-----+----
+ 11 | 242 | 22
+(1 row)
+
CREATE TABLE gtestxx_1 (a int NOT NULL, b int);
ALTER TABLE gtestxx_1 INHERIT gtest1; -- error
ERROR: column "b" in child table must be a generated column
@@ -541,7 +607,7 @@ CREATE FUNCTION gf1(a int) RETURNS int AS $$ SELECT a * 3 $$ IMMUTABLE LANGUAGE
REVOKE ALL ON FUNCTION gf1(int) FROM PUBLIC;
CREATE TABLE gtest12 (a int PRIMARY KEY, b int, c int GENERATED ALWAYS AS (gf1(b)) STORED);
INSERT INTO gtest12 VALUES (1, 10), (2, 20);
-GRANT SELECT (a, c) ON gtest12 TO regress_user11;
+GRANT SELECT (a, c), INSERT ON gtest12 TO regress_user11;
SET ROLE regress_user11;
SELECT a, b FROM gtest11; -- not allowed
ERROR: permission denied for table gtest11
@@ -554,7 +620,9 @@ SELECT a, c FROM gtest11; -- allowed
SELECT gf1(10); -- not allowed
ERROR: permission denied for function gf1
-SELECT a, c FROM gtest12; -- allowed
+INSERT INTO gtest12 VALUES (3, 30), (4, 40); -- currently not allowed because of function permissions, should arguably be allowed
+ERROR: permission denied for function gf1
+SELECT a, c FROM gtest12; -- allowed (does not actually invoke the function)
a | c
---+----
1 | 30
@@ -589,6 +657,13 @@ INSERT INTO gtest20b (a) VALUES (30);
ALTER TABLE gtest20b ADD CONSTRAINT chk CHECK (b < 50) NOT VALID;
ALTER TABLE gtest20b VALIDATE CONSTRAINT chk; -- fails on existing row
ERROR: check constraint "chk" of relation "gtest20b" is violated by some row
+-- check with whole-row reference
+CREATE TABLE gtest20c (a int, b int GENERATED ALWAYS AS (a * 2) STORED);
+ALTER TABLE gtest20c ADD CONSTRAINT whole_row_check CHECK (gtest20c IS NOT NULL);
+INSERT INTO gtest20c VALUES (1); -- ok
+INSERT INTO gtest20c VALUES (NULL); -- fails
+ERROR: new row for relation "gtest20c" violates check constraint "whole_row_check"
+DETAIL: Failing row contains (null, null).
-- not-null constraints
CREATE TABLE gtest21a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (nullif(a, 0)) STORED NOT NULL);
INSERT INTO gtest21a (a) VALUES (1); -- ok
@@ -758,6 +833,11 @@ CREATE TABLE gtest24 (a int PRIMARY KEY, b gtestdomain1 GENERATED ALWAYS AS (a *
INSERT INTO gtest24 (a) VALUES (4); -- ok
INSERT INTO gtest24 (a) VALUES (6); -- error
ERROR: value for domain gtestdomain1 violates check constraint "gtestdomain1_check"
+CREATE TYPE gtestdomain1range AS range (subtype = gtestdomain1);
+CREATE TABLE gtest24r (a int PRIMARY KEY, b gtestdomain1range GENERATED ALWAYS AS (gtestdomain1range(a, a + 5)) STORED);
+INSERT INTO gtest24r (a) VALUES (4); -- ok
+INSERT INTO gtest24r (a) VALUES (6); -- error
+ERROR: value for domain gtestdomain1 violates check constraint "gtestdomain1_check"
-- typed tables (currently not supported)
CREATE TYPE gtest_type AS (f1 integer, f2 text, f3 bigint);
CREATE TABLE gtest28 OF gtest_type (f1 WITH OPTIONS GENERATED ALWAYS AS (f2 *2) STORED);
@@ -841,6 +921,24 @@ SELECT tableoid::regclass, * FROM gtest_parent ORDER BY 1, 2, 3;
gtest_child2 | 08-15-2016 | 3 | 66
(3 rows)
+SELECT tableoid::regclass, * FROM gtest_child ORDER BY 1, 2, 3;
+ tableoid | f1 | f2 | f3
+-------------+------------+----+----
+ gtest_child | 07-15-2016 | 1 | 2
+ gtest_child | 07-15-2016 | 2 | 4
+(2 rows)
+
+SELECT tableoid::regclass, * FROM gtest_child2 ORDER BY 1, 2, 3;
+ tableoid | f1 | f2 | f3
+--------------+------------+----+----
+ gtest_child2 | 08-15-2016 | 3 | 66
+(1 row)
+
+SELECT tableoid::regclass, * FROM gtest_child3 ORDER BY 1, 2, 3;
+ tableoid | f1 | f2 | f3
+----------+----+----+----
+(0 rows)
+
UPDATE gtest_parent SET f1 = f1 + 60 WHERE f2 = 1;
SELECT tableoid::regclass, * FROM gtest_parent ORDER BY 1, 2, 3;
tableoid | f1 | f2 | f3
@@ -1184,6 +1282,18 @@ Inherits: gtest30
ALTER TABLE gtest30_1 ALTER COLUMN b DROP EXPRESSION; -- error
ERROR: cannot drop generation expression from inherited column
+-- composite type dependencies
+CREATE TABLE gtest31_1 (a int, b text GENERATED ALWAYS AS ('hello') STORED, c text);
+CREATE TABLE gtest31_2 (x int, y gtest31_1);
+ALTER TABLE gtest31_1 ALTER COLUMN b TYPE varchar; -- fails
+ERROR: cannot alter table "gtest31_1" because column "gtest31_2.y" uses its row type
+DROP TABLE gtest31_1, gtest31_2;
+-- Check it for a partitioned table, too
+CREATE TABLE gtest31_1 (a int, b text GENERATED ALWAYS AS ('hello') STORED, c text) PARTITION BY LIST (a);
+CREATE TABLE gtest31_2 (x int, y gtest31_1);
+ALTER TABLE gtest31_1 ALTER COLUMN b TYPE varchar; -- fails
+ERROR: cannot alter table "gtest31_1" because column "gtest31_2.y" uses its row type
+DROP TABLE gtest31_1, gtest31_2;
-- triggers
CREATE TABLE gtest26 (
a int PRIMARY KEY,
@@ -1294,7 +1404,7 @@ UPDATE gtest26 SET a = 1 WHERE a = 0;
NOTICE: OK
DROP TRIGGER gtest11 ON gtest26;
TRUNCATE gtest26;
--- check that modifications of stored generated columns in triggers do
+-- check that modifications of generated columns in triggers do
-- not get propagated
CREATE FUNCTION gtest_trigger_func4() RETURNS trigger
LANGUAGE plpgsql
@@ -1305,20 +1415,28 @@ BEGIN
RETURN NEW;
END;
$$;
-CREATE TRIGGER gtest12_01 BEFORE UPDATE ON gtest26
+CREATE TRIGGER gtest12_01 BEFORE INSERT OR UPDATE ON gtest26
FOR EACH ROW
EXECUTE PROCEDURE gtest_trigger_func();
-CREATE TRIGGER gtest12_02 BEFORE UPDATE ON gtest26
+CREATE TRIGGER gtest12_02 BEFORE INSERT OR UPDATE ON gtest26
FOR EACH ROW
EXECUTE PROCEDURE gtest_trigger_func4();
-CREATE TRIGGER gtest12_03 BEFORE UPDATE ON gtest26
+CREATE TRIGGER gtest12_03 BEFORE INSERT OR UPDATE ON gtest26
FOR EACH ROW
EXECUTE PROCEDURE gtest_trigger_func();
INSERT INTO gtest26 (a) VALUES (1);
-UPDATE gtest26 SET a = 11 WHERE a = 1;
-INFO: gtest12_01: BEFORE: old = (1,2)
+INFO: gtest12_01: BEFORE: new = (1,)
+INFO: gtest12_03: BEFORE: new = (10,300)
+SELECT * FROM gtest26 ORDER BY a;
+ a | b
+----+----
+ 10 | 20
+(1 row)
+
+UPDATE gtest26 SET a = 11 WHERE a = 10;
+INFO: gtest12_01: BEFORE: old = (10,20)
INFO: gtest12_01: BEFORE: new = (11,)
-INFO: gtest12_03: BEFORE: old = (1,2)
+INFO: gtest12_03: BEFORE: old = (10,20)
INFO: gtest12_03: BEFORE: new = (10,)
SELECT * FROM gtest26 ORDER BY a;
a | b
diff --git a/src/test/regress/sql/generated_stored.sql b/src/test/regress/sql/generated_stored.sql
index ec45c93d180..6fbfcbf9615 100644
--- a/src/test/regress/sql/generated_stored.sql
+++ b/src/test/regress/sql/generated_stored.sql
@@ -56,6 +56,8 @@ INSERT INTO gtest1 VALUES (3, 33), (4, DEFAULT); -- error
INSERT INTO gtest1 VALUES (3, DEFAULT), (4, DEFAULT); -- ok
SELECT * FROM gtest1 ORDER BY a;
+SELECT gtest1 FROM gtest1 ORDER BY a; -- whole-row reference
+SELECT a, (SELECT gtest1.b) FROM gtest1 ORDER BY a; -- sublink
DELETE FROM gtest1 WHERE a >= 3;
UPDATE gtest1 SET b = DEFAULT WHERE a = 1;
@@ -79,7 +81,7 @@ DROP TABLE gtestx;
-- test UPDATE/DELETE quals
SELECT * FROM gtest1 ORDER BY a;
-UPDATE gtest1 SET a = 3 WHERE b = 4;
+UPDATE gtest1 SET a = 3 WHERE b = 4 RETURNING old.*, new.*;
SELECT * FROM gtest1 ORDER BY a;
DELETE FROM gtest1 WHERE b = 2;
SELECT * FROM gtest1 ORDER BY a;
@@ -95,10 +97,19 @@ CREATE TABLE gtestm (
INSERT INTO gtestm VALUES (1, 5, 100);
MERGE INTO gtestm t USING (VALUES (1, 10), (2, 20)) v(id, f1) ON t.id = v.id
WHEN MATCHED THEN UPDATE SET f1 = v.f1
- WHEN NOT MATCHED THEN INSERT VALUES (v.id, v.f1, 200);
+ WHEN NOT MATCHED THEN INSERT VALUES (v.id, v.f1, 200)
+ RETURNING merge_action(), old.*, new.*;
SELECT * FROM gtestm ORDER BY id;
DROP TABLE gtestm;
+CREATE TABLE gtestm (
+ a int PRIMARY KEY,
+ b int GENERATED ALWAYS AS (a * 2) STORED
+);
+INSERT INTO gtestm (a) SELECT g FROM generate_series(1, 10) g;
+MERGE INTO gtestm t USING gtestm AS s ON 2 * t.a = s.b WHEN MATCHED THEN DELETE RETURNING *;
+DROP TABLE gtestm;
+
-- views
CREATE VIEW gtest1v AS SELECT * FROM gtest1;
SELECT * FROM gtest1v;
@@ -140,6 +151,9 @@ CREATE TABLE gtestx (x int, b int DEFAULT 10) INHERITS (gtest1); -- error
CREATE TABLE gtestx (x int, b int GENERATED ALWAYS AS IDENTITY) INHERITS (gtest1); -- error
CREATE TABLE gtestx (x int, b int GENERATED ALWAYS AS (a * 22) STORED) INHERITS (gtest1); -- ok, overrides parent
\d+ gtestx
+INSERT INTO gtestx (a, x) VALUES (11, 22);
+SELECT * FROM gtest1;
+SELECT * FROM gtestx;
CREATE TABLE gtestxx_1 (a int NOT NULL, b int);
ALTER TABLE gtestxx_1 INHERIT gtest1; -- error
@@ -274,13 +288,14 @@ REVOKE ALL ON FUNCTION gf1(int) FROM PUBLIC;
CREATE TABLE gtest12 (a int PRIMARY KEY, b int, c int GENERATED ALWAYS AS (gf1(b)) STORED);
INSERT INTO gtest12 VALUES (1, 10), (2, 20);
-GRANT SELECT (a, c) ON gtest12 TO regress_user11;
+GRANT SELECT (a, c), INSERT ON gtest12 TO regress_user11;
SET ROLE regress_user11;
SELECT a, b FROM gtest11; -- not allowed
SELECT a, c FROM gtest11; -- allowed
SELECT gf1(10); -- not allowed
-SELECT a, c FROM gtest12; -- allowed
+INSERT INTO gtest12 VALUES (3, 30), (4, 40); -- currently not allowed because of function permissions, should arguably be allowed
+SELECT a, c FROM gtest12; -- allowed (does not actually invoke the function)
RESET ROLE;
DROP FUNCTION gf1(int); -- fail
@@ -307,6 +322,12 @@ INSERT INTO gtest20b (a) VALUES (30);
ALTER TABLE gtest20b ADD CONSTRAINT chk CHECK (b < 50) NOT VALID;
ALTER TABLE gtest20b VALIDATE CONSTRAINT chk; -- fails on existing row
+-- check with whole-row reference
+CREATE TABLE gtest20c (a int, b int GENERATED ALWAYS AS (a * 2) STORED);
+ALTER TABLE gtest20c ADD CONSTRAINT whole_row_check CHECK (gtest20c IS NOT NULL);
+INSERT INTO gtest20c VALUES (1); -- ok
+INSERT INTO gtest20c VALUES (NULL); -- fails
+
-- not-null constraints
CREATE TABLE gtest21a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (nullif(a, 0)) STORED NOT NULL);
INSERT INTO gtest21a (a) VALUES (1); -- ok
@@ -386,6 +407,10 @@ CREATE DOMAIN gtestdomain1 AS int CHECK (VALUE < 10);
CREATE TABLE gtest24 (a int PRIMARY KEY, b gtestdomain1 GENERATED ALWAYS AS (a * 2) STORED);
INSERT INTO gtest24 (a) VALUES (4); -- ok
INSERT INTO gtest24 (a) VALUES (6); -- error
+CREATE TYPE gtestdomain1range AS range (subtype = gtestdomain1);
+CREATE TABLE gtest24r (a int PRIMARY KEY, b gtestdomain1range GENERATED ALWAYS AS (gtestdomain1range(a, a + 5)) STORED);
+INSERT INTO gtest24r (a) VALUES (4); -- ok
+INSERT INTO gtest24r (a) VALUES (6); -- error
-- typed tables (currently not supported)
CREATE TYPE gtest_type AS (f1 integer, f2 text, f3 bigint);
@@ -431,6 +456,9 @@ INSERT INTO gtest_parent (f1, f2) VALUES ('2016-07-15', 1);
INSERT INTO gtest_parent (f1, f2) VALUES ('2016-07-15', 2);
INSERT INTO gtest_parent (f1, f2) VALUES ('2016-08-15', 3);
SELECT tableoid::regclass, * FROM gtest_parent ORDER BY 1, 2, 3;
+SELECT tableoid::regclass, * FROM gtest_child ORDER BY 1, 2, 3;
+SELECT tableoid::regclass, * FROM gtest_child2 ORDER BY 1, 2, 3;
+SELECT tableoid::regclass, * FROM gtest_child3 ORDER BY 1, 2, 3;
UPDATE gtest_parent SET f1 = f1 + 60 WHERE f2 = 1;
SELECT tableoid::regclass, * FROM gtest_parent ORDER BY 1, 2, 3;
@@ -545,6 +573,18 @@ ALTER TABLE ONLY gtest30 ALTER COLUMN b DROP EXPRESSION; -- error
\d gtest30_1
ALTER TABLE gtest30_1 ALTER COLUMN b DROP EXPRESSION; -- error
+-- composite type dependencies
+CREATE TABLE gtest31_1 (a int, b text GENERATED ALWAYS AS ('hello') STORED, c text);
+CREATE TABLE gtest31_2 (x int, y gtest31_1);
+ALTER TABLE gtest31_1 ALTER COLUMN b TYPE varchar; -- fails
+DROP TABLE gtest31_1, gtest31_2;
+
+-- Check it for a partitioned table, too
+CREATE TABLE gtest31_1 (a int, b text GENERATED ALWAYS AS ('hello') STORED, c text) PARTITION BY LIST (a);
+CREATE TABLE gtest31_2 (x int, y gtest31_1);
+ALTER TABLE gtest31_1 ALTER COLUMN b TYPE varchar; -- fails
+DROP TABLE gtest31_1, gtest31_2;
+
-- triggers
CREATE TABLE gtest26 (
a int PRIMARY KEY,
@@ -630,7 +670,7 @@ UPDATE gtest26 SET a = 1 WHERE a = 0;
DROP TRIGGER gtest11 ON gtest26;
TRUNCATE gtest26;
--- check that modifications of stored generated columns in triggers do
+-- check that modifications of generated columns in triggers do
-- not get propagated
CREATE FUNCTION gtest_trigger_func4() RETURNS trigger
LANGUAGE plpgsql
@@ -642,20 +682,21 @@ BEGIN
END;
$$;
-CREATE TRIGGER gtest12_01 BEFORE UPDATE ON gtest26
+CREATE TRIGGER gtest12_01 BEFORE INSERT OR UPDATE ON gtest26
FOR EACH ROW
EXECUTE PROCEDURE gtest_trigger_func();
-CREATE TRIGGER gtest12_02 BEFORE UPDATE ON gtest26
+CREATE TRIGGER gtest12_02 BEFORE INSERT OR UPDATE ON gtest26
FOR EACH ROW
EXECUTE PROCEDURE gtest_trigger_func4();
-CREATE TRIGGER gtest12_03 BEFORE UPDATE ON gtest26
+CREATE TRIGGER gtest12_03 BEFORE INSERT OR UPDATE ON gtest26
FOR EACH ROW
EXECUTE PROCEDURE gtest_trigger_func();
INSERT INTO gtest26 (a) VALUES (1);
-UPDATE gtest26 SET a = 11 WHERE a = 1;
+SELECT * FROM gtest26 ORDER BY a;
+UPDATE gtest26 SET a = 11 WHERE a = 10;
SELECT * FROM gtest26 ORDER BY a;
-- LIKE INCLUDING GENERATED and dropped column handling
generated by cgit v1.2.3 (git 2.25.1) at 2025-05-28 19:13:49 +0000