From fc22b6623b6b3bab3cb057ccd282c2bfad1a0b30 Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <peter@eisentraut.org>
Date: Sat, 30 Mar 2019 08:13:09 +0100
Subject: Generated columns

This is an SQL-standard feature that allows creating columns that are
computed from expressions rather than assigned, similar to a view or
materialized view but on a column basis.

This implements one kind of generated column: stored (computed on
write).  Another kind, virtual (computed on read), is planned for the
future, and some room is left for it.

Reviewed-by: Michael Paquier <michael@paquier.xyz>
Reviewed-by: Pavel Stehule <pavel.stehule@gmail.com>
Discussion: https://www.postgresql.org/message-id/flat/b151f851-4019-bdb1-699e-ebab07d2f40a@2ndquadrant.com
---
 src/pl/plpython/sql/plpython_trigger.sql | 37 ++++++++++++++++++++++++++++++++
 1 file changed, 37 insertions(+)

(limited to 'src/pl/plpython/sql/plpython_trigger.sql')

diff --git a/src/pl/plpython/sql/plpython_trigger.sql b/src/pl/plpython/sql/plpython_trigger.sql
index 79c24b714b5..19852dc5851 100644
--- a/src/pl/plpython/sql/plpython_trigger.sql
+++ b/src/pl/plpython/sql/plpython_trigger.sql
@@ -67,6 +67,11 @@ SELECT * FROM users;
 CREATE TABLE trigger_test
 	(i int, v text );
 
+CREATE TABLE trigger_test_generated (
+	i int,
+        j int GENERATED ALWAYS AS (i * 2) STORED
+);
+
 CREATE FUNCTION trigger_data() RETURNS trigger LANGUAGE plpythonu AS $$
 
 if 'relid' in TD:
@@ -109,6 +114,21 @@ DROP TRIGGER show_trigger_data_trig_stmt on trigger_test;
 DROP TRIGGER show_trigger_data_trig_before on trigger_test;
 DROP TRIGGER show_trigger_data_trig_after on trigger_test;
 
+CREATE TRIGGER show_trigger_data_trig_before
+BEFORE INSERT OR UPDATE OR DELETE ON trigger_test_generated
+FOR EACH ROW EXECUTE PROCEDURE trigger_data();
+
+CREATE TRIGGER show_trigger_data_trig_after
+AFTER INSERT OR UPDATE OR DELETE ON trigger_test_generated
+FOR EACH ROW EXECUTE PROCEDURE trigger_data();
+
+insert into trigger_test_generated (i) values (1);
+update trigger_test_generated set i = 11 where i = 1;
+delete from trigger_test_generated;
+
+DROP TRIGGER show_trigger_data_trig_before ON trigger_test_generated;
+DROP TRIGGER show_trigger_data_trig_after ON trigger_test_generated;
+
 insert into trigger_test values(1,'insert');
 CREATE VIEW trigger_test_view AS SELECT * FROM trigger_test;
 
@@ -430,3 +450,20 @@ UPDATE transition_table_test SET name = 'b';
 
 DROP TABLE transition_table_test;
 DROP FUNCTION transition_table_test_f();
+
+
+-- dealing with generated columns
+
+CREATE FUNCTION generated_test_func1() RETURNS trigger
+LANGUAGE plpythonu
+AS $$
+TD['new']['j'] = 5  # not allowed
+return 'MODIFY'
+$$;
+
+CREATE TRIGGER generated_test_trigger1 BEFORE INSERT ON trigger_test_generated
+FOR EACH ROW EXECUTE PROCEDURE generated_test_func1();
+
+TRUNCATE trigger_test_generated;
+INSERT INTO trigger_test_generated (i) VALUES (1);
+SELECT * FROM trigger_test_generated;
-- 
cgit v1.2.3

