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

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

---+---

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

----+----+-----+----+-----

1 | 10 | 100 | 20 | 200

2 | 20 | 200 | 40 | 400

(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;

"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

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

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

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

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);

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

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,

NOTICE: OK

DROP TRIGGER gtest11 ON gtest26;

TRUNCATE gtest26;

-- check that modifications ofstoredgenerated 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

RETURN NEW;

END;

$$;

CREATE TRIGGER gtest12_01 BEFORE UPDATE ON gtest26

CREATE TRIGGER gtest12_01 BEFOREINSERT ORUPDATE ON gtest26

FOR EACH ROW

EXECUTE PROCEDURE gtest_trigger_func();

CREATE TRIGGER gtest12_02 BEFORE UPDATE ON gtest26

CREATE TRIGGER gtest12_02 BEFOREINSERT ORUPDATE ON gtest26

FOR EACH ROW

EXECUTE PROCEDURE gtest_trigger_func4();

CREATE TRIGGER gtest12_03 BEFORE UPDATE ON gtest26

CREATE TRIGGER gtest12_03 BEFOREINSERT ORUPDATE 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

INSERT INTO gtest1VALUES (3, DEFAULT), (4, DEFAULT);-- ok

SELECT*FROM gtest1ORDER BY a;

SELECT gtest1FROM gtest1ORDER BY a;-- whole-row reference

SELECT a, (SELECTgtest1.b)FROM gtest1ORDER BY a;-- sublink

DELETEFROM gtest1WHERE a>=3;

UPDATE gtest1SET b= DEFAULTWHERE a=1;

SELECT*FROM gtest1ORDER BY a;

UPDATE gtest1SET a=3WHERE b=4;

UPDATE gtest1SET a=3WHERE b=4 RETURNING old.*, new.*;

SELECT*FROM gtest1ORDER BY a;

DELETEFROM gtest1WHERE b=2;

SELECT*FROM gtest1ORDER BY a;

INSERT INTO gtestmVALUES (1,5,100);

MERGE INTO gtestm t USING (VALUES (1,10), (2,20)) v(id, f1)ONt.id=v.id

WHEN MATCHED THENUPDATESET f1=v.f1

WHEN NOT MATCHED THEN INSERTVALUES (v.id,v.f1,200);

WHEN NOT MATCHED THEN INSERTVALUES (v.id,v.f1,200)

RETURNING merge_action(), old.*, new.*;

SELECT*FROM gtestmORDER BY id;

DROPTABLE gtestm;

CREATETABLEgtestm (

aintPRIMARY KEY,

bint GENERATED ALWAYSAS (a*2) STORED

);

INSERT INTO gtestm (a)SELECT gFROM generate_series(1,10) g;

MERGE INTO gtestm t USING gtestmAS sON2*t.a=s.b WHEN MATCHED THENDELETE RETURNING*;

DROPTABLE gtestm;

CREATEVIEWgtest1vASSELECT*FROM gtest1;

SELECT*FROM gtest1v;

CREATETABLEgtestx (xint, bint GENERATED ALWAYSAS IDENTITY) INHERITS (gtest1);-- error

CREATETABLEgtestx (xint, bint GENERATED ALWAYSAS (a*22) STORED) INHERITS (gtest1);-- ok, overrides parent

\d+ gtestx

INSERT INTO gtestx (a, x)VALUES (11,22);

SELECT*FROM gtest1;

SELECT*FROM gtestx;

CREATETABLEgtestxx_1 (aintNOT NULL, bint);

ALTERTABLE gtestxx_1 INHERIT gtest1;-- error

CREATETABLEgtest12 (aintPRIMARY KEY, bint, cint GENERATED ALWAYSAS (gf1(b)) STORED);

INSERT INTO gtest12VALUES (1,10), (2,20);

GRANTSELECT (a, c)ON gtest12 TO regress_user11;

GRANTSELECT (a, c), INSERTON gtest12 TO regress_user11;

SET ROLE regress_user11;

SELECT a, bFROM gtest11;-- not allowed

SELECT a, cFROM gtest11;-- allowed

SELECT gf1(10);-- not allowed

SELECT a, cFROM gtest12;-- allowed

INSERT INTO gtest12VALUES (3,30), (4,40);-- currently not allowed because of function permissions, should arguably be allowed

SELECT a, cFROM gtest12;-- allowed (does not actually invoke the function)

RESET ROLE;

DROPFUNCTION gf1(int);-- fail

ALTERTABLE gtest20b ADDCONSTRAINT chkCHECK (b<50) NOT VALID;

ALTERTABLE gtest20b VALIDATECONSTRAINT chk;-- fails on existing row

CREATETABLEgtest20c (aint, bint GENERATED ALWAYSAS (a*2) STORED);

ALTERTABLE gtest20c ADDCONSTRAINT whole_row_checkCHECK (gtest20cIS NOT NULL);

INSERT INTO gtest20cVALUES (1);-- ok

INSERT INTO gtest20cVALUES (NULL);-- fails

CREATETABLEgtest21a (aintPRIMARY KEY, bint GENERATED ALWAYSAS (nullif(a,0)) STOREDNOT NULL);

INSERT INTO gtest21a (a)VALUES (1);-- ok

CREATETABLEgtest24 (aintPRIMARY KEY, b gtestdomain1 GENERATED ALWAYSAS (a*2) STORED);

INSERT INTO gtest24 (a)VALUES (4);-- ok

INSERT INTO gtest24 (a)VALUES (6);-- error

CREATETYPEgtestdomain1rangeAS range (subtype= gtestdomain1);

CREATETABLEgtest24r (aintPRIMARY KEY, b gtestdomain1range GENERATED ALWAYSAS (gtestdomain1range(a, a+5)) STORED);

INSERT INTO gtest24r (a)VALUES (4);-- ok

INSERT INTO gtest24r (a)VALUES (6);-- error

CREATETYPEgtest_typeAS (f1integer, f2text, f3bigint);

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_parentORDER BY1,2,3;

SELECT tableoid::regclass,*FROM gtest_childORDER BY1,2,3;

SELECT tableoid::regclass,*FROM gtest_child2ORDER BY1,2,3;

SELECT tableoid::regclass,*FROM gtest_child3ORDER BY1,2,3;

UPDATE gtest_parentSET f1= f1+60WHERE f2=1;

SELECT tableoid::regclass,*FROM gtest_parentORDER BY1,2,3;

\d gtest30_1

ALTERTABLE gtest30_1 ALTER COLUMN b DROP EXPRESSION;-- error

CREATETABLEgtest31_1 (aint, btext GENERATED ALWAYSAS ('hello') STORED, ctext);

CREATETABLEgtest31_2 (xint, y gtest31_1);

ALTERTABLE gtest31_1 ALTER COLUMN b TYPEvarchar;-- fails

DROPTABLE gtest31_1, gtest31_2;

CREATETABLEgtest31_1 (aint, btext GENERATED ALWAYSAS ('hello') STORED, ctext) PARTITION BY LIST (a);

CREATETABLEgtest31_2 (xint, y gtest31_1);

ALTERTABLE gtest31_1 ALTER COLUMN b TYPEvarchar;-- fails

DROPTABLE gtest31_1, gtest31_2;

CREATETABLEgtest26 (

aintPRIMARY KEY,

DROPTRIGGER gtest11ON gtest26;

TRUNCATE gtest26;

CREATEFUNCTIONgtest_trigger_func4() RETURNS trigger

LANGUAGE plpgsql

END;

$$;

CREATETRIGGERgtest12_01 BEFOREUPDATEON gtest26

CREATETRIGGERgtest12_01 BEFOREINSERTORUPDATEON gtest26

FOR EACH ROW

EXECUTE PROCEDURE gtest_trigger_func();

CREATETRIGGERgtest12_02 BEFOREUPDATEON gtest26

CREATETRIGGERgtest12_02 BEFOREINSERTORUPDATEON gtest26

FOR EACH ROW

EXECUTE PROCEDURE gtest_trigger_func4();

CREATETRIGGERgtest12_03 BEFOREUPDATEON gtest26

CREATETRIGGERgtest12_03 BEFOREINSERTORUPDATEON gtest26

FOR EACH ROW

EXECUTE PROCEDURE gtest_trigger_func();

INSERT INTO gtest26 (a)VALUES (1);

UPDATE gtest26SET a=11WHERE a=1;

SELECT*FROM gtest26ORDER BY a;

UPDATE gtest26SET a=11WHERE a=10;

SELECT*FROM gtest26ORDER BY a;