Re: Best practice for naming temp table trigger functions

Hello!

Using pg_temp as schema prefix for trigger function and sequence seems to do the job...

Just need confirmation that this is valid PostgreSQL code...

Tx!
Seb

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)