https://vibhorkumar.blog/2017/07/06/leveraging-local-temporary-table-for-global-temporary-table-in-edb-postgres-9-6/
The code example only covers the INSERT case, so here is an extension on that code which includes INSERT/UPDATE/DELETE:
CREATE UNLOGGED TABLE gtt_test_definition (id int);
CREATE VIEW gtt_test AS SELECT * FROM gtt_test_definition;
CREATE OR REPLACE FUNCTION gtt_test_function()
RETURNS TRIGGER
LANGUAGE plpgsql
AS
$function$
BEGIN
/* attempt to run the DML */
IF (TG_OP = 'INSERT') THEN
INSERT INTO gtt_test_temp VALUES(NEW.*);
RETURN NEW;
ELSIF (TG_OP = 'UPDATE') THEN
UPDATE gtt_test_temp SET id = NEW.id WHERE id = OLD.id;
RETURN NEW;
ELSIF (TG_OP = 'DELETE') THEN
DELETE FROM gtt_test_temp WHERE id = OLD.id;
RETURN NEW;
END IF;
/* when the temp table doesn't already exist */
EXCEPTION WHEN undefined_table THEN
/* create local temporary table */
CREATE TEMP TABLE gtt_test_temp
(LIKE gtt_test_definition INCLUDING ALL )
INHERITS (gtt_test_definition)
ON COMMIT DELETE ROWS;
/* re-run the DML */
IF (TG_OP = 'INSERT') THEN
INSERT INTO gtt_test_temp VALUES(NEW.*);
RETURN NEW;
ELSIF (TG_OP = 'UPDATE') THEN
UPDATE gtt_test_temp SET id = NEW.id WHERE id = OLD.id;
RETURN NEW;
ELSIF (TG_OP = 'DELETE') THEN
DELETE FROM gtt_test_temp WHERE id = OLD.id;
RETURN NEW;
END IF;
END;
$function$;
CREATE TRIGGER gtt_test_trigger INSTEAD OF INSERT OR UPDATE OR DELETE ON gtt_test
FOR EACH ROW EXECUTE PROCEDURE gtt_test_function();
And here is a test case, showing it working for ON COMMIT DELETE:
postgres=# \d
No relations found.
postgres=# \i test_gtt.sql
CREATE TABLE
CREATE VIEW
CREATE FUNCTION
CREATE TRIGGER
postgres=# \d
List of relations
Schema | Name | Type | Owner
--------+---------------------+-------+----------
public | gtt_test | view | postgres
public | gtt_test_definition | table | postgres
(2 rows)
postgres=# select * from gtt_test;
id
----
(0 rows)
postgres=# insert into gtt_test values(1);
NOTICE: merging column "id" with inherited definition
INSERT 0 1
postgres=# select * from gtt_test;
id
----
(0 rows)
postgres=# \d
List of relations
Schema | Name | Type | Owner
-----------+---------------------+-------+----------
pg_temp_2 | gtt_test_temp | table | postgres
public | gtt_test | view | postgres
public | gtt_test_definition | table | postgres
(3 rows)
postgres=# begin;
BEGIN
postgres=# insert into gtt_test values(1);
INSERT 0 1
postgres=# select * from gtt_test;
id
----
1
(1 row)
postgres=# insert into gtt_test values(2);
INSERT 0 1
postgres=# select * from gtt_test;
id
----
1
2
(2 rows)
postgres=# commit;
COMMIT
postgres=# select * from gtt_test;
id
----
(0 rows)
postgres=# begin;
BEGIN
postgres=# insert into gtt_test values(1);
INSERT 0 1
postgres=# insert into gtt_test values(2);
INSERT 0 1
postgres=# select * from gtt_test;
id
----
1
2
(2 rows)
postgres=# update gtt_test set id=100 where id=1;
UPDATE 1
postgres=# select * from gtt_test;
id
-----
2
100
(2 rows)
postgres=# commit;
COMMIT
postgres=# select * from gtt_test;
id
----
(0 rows)
postgres=# begin;
BEGIN
postgres=# insert into gtt_test values(1);
INSERT 0 1
postgres=# insert into gtt_test values(2);
INSERT 0 1
postgres=# insert into gtt_test values(3);
INSERT 0 1
postgres=# select * from gtt_test;
id
----
1
2
3
(3 rows)
postgres=# delete from gtt_test where id=2;
DELETE 0
postgres=# select * from gtt_test;
id
----
1
3
(2 rows)
postgres=# commit;
COMMIT
postgres=# select * from gtt_test;
id
----
(0 rows)
No comments:
Post a Comment