pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Sebastien Flaesch <sebastien.flaesch@4js.com>
To: David G. Johnston <david.g.johnston@gmail.com>
Cc: pgsql-sql@lists.postgresql.org <pgsql-sql@lists.postgresql.org>
Subject: Re: Best practice for naming temp table trigger functions
Date: Wed, 9 Mar 2022 08:26:30 +0000
Message-ID: <DBAP191MB128960431DC469430A58F521B00A9@DBAP191MB1289.EURP191.PROD.OUTLOOK.COM> (raw)
In-Reply-To: <DBAP191MB12891233EFFBA6A4CC30E420B0099@DBAP191MB1289.EURP191.PROD.OUTLOOK.COM>
References: <DBAP191MB128959B0FE1ABE28C04ACDFFB0089@DBAP191MB1289.EURP191.PROD.OUTLOOK.COM>
	<DBAP191MB1289C893FE38B9C831465376B0099@DBAP191MB1289.EURP191.PROD.OUTLOOK.COM>
	<CAKFQuwZNZzgpo+P8bF6HG=VMq9ZZaw2kpvpgq1KaaL59NFVUTw@mail.gmail.com>
	<DBAP191MB12891233EFFBA6A4CC30E420B0099@DBAP191MB1289.EURP191.PROD.OUTLOOK.COM>

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)

view thread (5+ messages)

Message-ID: <DBAP191MB128960431DC469430A58F521B00A9@DBAP191MB1289.EURP191.PROD.OUTLOOK.COM>
Permalink:  ../DBAP191MB128960431DC469430A58F521B00A9@DBAP191MB1289.EURP191.PROD.OUTLOOK.COM/
Also on:    postgresql.org/message-id/DBAP191MB128960431DC469430A58F521B00A9@DBAP191MB1289.EURP191.PROD.OUTLOOK.COM

 · 

reply
Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: sebastien.flaesch@4js.com, david.g.johnston@gmail.com, pgsql-sql@lists.postgresql.org
  Subject: Re: Best practice for naming temp table trigger functions
  In-Reply-To: <DBAP191MB128960431DC469430A58F521B00A9@DBAP191MB1289.EURP191.PROD.OUTLOOK.COM>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox