PostgreSQL and temp table triggers-CodePudding

I am looking for the best practice to define triggers and sequences on temporary tables with PostgreSQL.

When creating a temp table, PostgreSQL automatically creates a temporary schema with the name "pg_temp_nnn" (alias: "pg_temp")

It appears that one can create user functions and objects in this temporary schema.

I wonder if this is really valid SQL for PostgreSQL or just working by accident?

Tested with various PostgreSQL versions from 10 to 14.

Note: Triggers created on temp tables automatically land in the temp schema because the trigger inherits the schema of its table.

Tx!

CREATE TEMP TABLE tt1 (pk INTEGER NOT NULL, name VARCHAR(50));
 
CREATE SEQUENCE pg_temp.tt1_seq START 1;

CREATE FUNCTION pg_temp.tt1_srl() RETURNS TRIGGER AS
'DECLARE ls BIGINT;
  BEGIN
     SELECT INTO ls nextval(''pg_temp.tt1_seq'');
     IF new.pk ISNULL OR new.pk=0 THEN
        new.pk:=ls;
     ELSE
        IF new.pk>=ls THEN
           PERFORM setval(''pg_temp.tt1_seq'',new.pk);
        END IF;
     END IF;
     RETURN new;
  END;'
LANGUAGE 'plpgsql';

CREATE TRIGGER tt1_srlt BEFORE INSERT ON tt1 FOR EACH ROW EXECUTE PROCEDURE pg_temp.tt1_srl();

INSERT INTO tt1 (name) VALUES ('aaaa');
SELECT 'Insert #1:', currval('pg_temp.tt1_seq');

INSERT INTO tt1 VALUES (0,'bbbb');
SELECT 'Insert #2:', currval('pg_temp.tt1_seq');

INSERT INTO tt1 VALUES (100,'cccc');
SELECT 'Insert #3:', currval('pg_temp.tt1_seq');

INSERT INTO tt1 (name) VALUES ('dddd');
SELECT 'Insert #4:', currval('pg_temp.tt1_seq');

SELECT * FROM tt1 ORDER BY pk;



Output:


CREATE TABLE
CREATE SEQUENCE
CREATE FUNCTION
CREATE TRIGGER
INSERT 0 1
  ?column?  | currval
------------ ---------
 Insert #1: |       1
(1 row)

INSERT 0 1
  ?column?  | currval
------------ ---------
 Insert #2: |       2
(1 row)

INSERT 0 1
  ?column?  | currval
------------ ---------
 Insert #3: |     100
(1 row)

INSERT 0 1
  ?column?  | currval
------------ ---------
 Insert #4: |     101
(1 row)

 pk  | name
----- ------
   1 | aaaa
   2 | bbbb
 100 | cccc
 101 | dddd
(4 rows)

CodePudding user response：

Yes, that works and is supported.

Creating objects in schema pg_temp creates temporary objects that will be removed when the session ends. CREATE TEMP TABLE x (...) is the same as CREATE TABLE pg_temp.x (...).