Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1nRrep-0002iI-5F for pgsql-sql@arkaria.postgresql.org; Wed, 09 Mar 2022 08:26:55 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1nRren-0000gE-Rf for pgsql-sql@arkaria.postgresql.org; Wed, 09 Mar 2022 08:26:53 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1nRren-0000fI-Fl for pgsql-sql@lists.postgresql.org; Wed, 09 Mar 2022 08:26:53 +0000 Received: from mx0c-001a4c01.pphosted.com ([67.231.158.153]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1nRrec-0001HH-4p for pgsql-sql@lists.postgresql.org; Wed, 09 Mar 2022 08:26:45 +0000 Received: from pps.filterd (m0210019.ppops.net [127.0.0.1]) by mx0c-001a4c01.pphosted.com (8.16.1.2/8.16.1.2) with ESMTP id 228LJBAv013080; Wed, 9 Mar 2022 03:26:35 -0500 Authentication-Results: ppops.net; spf=pass smtp.mailfrom=sebastien.flaesch@4js.com; dkim=pass header.s=selector2-ourvolaris-onmicrosoft-com header.d=ourvolaris.onmicrosoft.com Received: from mail.ourvolaris.com ([206.25.40.41]) by mx0c-001a4c01.pphosted.com (PPS) with ESMTPS id 3em4pseb2k-1 (version=TLSv1.2 cipher=ECDHE-RSA-AES256-SHA384 bits=256 verify=NOT); Wed, 09 Mar 2022 03:26:34 -0500 Received: from VOL-SLO-EXCH2.vgnet.volgrp.com (172.23.0.52) by VOL-SLO-EXCH1.vgnet.volgrp.com (172.23.0.51) with Microsoft SMTP Server (TLS) id 15.0.1497.28; Wed, 9 Mar 2022 08:26:31 +0000 Received: from EUR05-DB8-obe.outbound.protection.outlook.com (104.47.17.111) by VOL-SLO-EXCH2.vgnet.volgrp.com (172.23.0.52) with Microsoft SMTP Server (TLS) id 15.0.1497.28 via Frontend Transport; Wed, 9 Mar 2022 08:26:31 +0000 ARC-Seal: i=1; a=rsa-sha256; s=arcselector9901; d=microsoft.com; cv=none; b=AoP9Ot0DcttpuZ/hRp899Zb6QRS/Ota9raxQH49SrEztyhQVGvwmpngwTTxYV8mPK7joLfboOrSq6j3wPU+nsNrS0MY8P+jfz0toOS7IliADnw2f4VZKmHivB1O6cMWlu2QbIyatMAA7uwgGCUFh212099+O+oU8k8M1ZR4NMn9KSWcgRO24tn9Dsze9cdv8LNsL7GWLDEaYoIpKaWPf9ZekwXuWJ+aIpbv2YNw3pv8oUc82c3cTSAyQrjR6jxYOgt7b+90b4qa8Pl0Swj3LSGh557L0MlFo3vSxHh72ud/Eh58t5xSjfof3U/+l48AA98/qwK4Zffv6M2cBZwDk+A== ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=microsoft.com; s=arcselector9901; h=From:Date:Subject:Message-ID:Content-Type:MIME-Version:X-MS-Exchange-AntiSpam-MessageData-ChunkCount:X-MS-Exchange-AntiSpam-MessageData-0:X-MS-Exchange-AntiSpam-MessageData-1; bh=O3wXRNVwsNQ0Y9b2rsw0/xnjJKcw2ijCBwqhqMhU1J0=; b=LCU64U8832AVyPDXdh5tRbVgxWc5t+rwLkFOrkPDTSoxkGR2VX1k6VJCbGozgXeysxviavxMqpMMsEHYCfkxkH4oyS5PRzquLN/kOszt6oQA3Dmbi2sg5Hsj9DP4weXrCcPPU2qm0LDXahHtcL2HTNDpL5YUIzMGwXQzC2W/BHzzcXoU21qaPWy9nKgZnAtSwoY6U3Zp15gR04r69mz8ppk00lsruJd4pnsVQGqK6/cNfWI8Ici2M5ZRbeaBBBhJb6cQCE7oQ+VoIM+TWYvPfQDFeR31YM/8+qFT6iskvm+BMBPuYWSc+AI4ZuzfN9osFN4l+NHKyQYikFWWUsCjLw== ARC-Authentication-Results: i=1; mx.microsoft.com 1; spf=pass smtp.mailfrom=4js.com; dmarc=pass action=none header.from=4js.com; dkim=pass header.d=4js.com; arc=none Received: from DBAP191MB1289.EURP191.PROD.OUTLOOK.COM (2603:10a6:10:1c9::19) by AS8P191MB1493.EURP191.PROD.OUTLOOK.COM (2603:10a6:20b:2eb::10) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384) id 15.20.5038.15; Wed, 9 Mar 2022 08:26:31 +0000 Received: from DBAP191MB1289.EURP191.PROD.OUTLOOK.COM ([fe80::a533:756:799f:8c50]) by DBAP191MB1289.EURP191.PROD.OUTLOOK.COM ([fe80::a533:756:799f:8c50%4]) with mapi id 15.20.5038.027; Wed, 9 Mar 2022 08:26:30 +0000 From: Sebastien Flaesch To: "David G. Johnston" CC: "pgsql-sql@lists.postgresql.org" Subject: Re: Best practice for naming temp table trigger functions Thread-Topic: Best practice for naming temp table trigger functions Thread-Index: AQHYMk3wKpd6RixG7ki7DRsQNFeEB6y1nug6gAAMJYCAAAt0IYABAsoO Date: Wed, 9 Mar 2022 08:26:30 +0000 Message-ID: References: In-Reply-To: Accept-Language: en-US Content-Language: en-US X-MS-Has-Attach: X-MS-TNEF-Correlator: suggested_attachment_session_id: c6b3769f-b57b-48d6-0b7c-35ba7eb1ce96 x-ms-publictraffictype: Email x-ms-office365-filtering-correlation-id: 61784641-5dfb-4f5a-6afc-08da01a68386 x-ms-traffictypediagnostic: AS8P191MB1493:EE_ x-microsoft-antispam-prvs: x-ms-exchange-senderadcheck: 1 x-ms-exchange-antispam-relay: 0 x-microsoft-antispam: BCL:0; x-microsoft-antispam-message-info: uDrdU4ocR+3qo/M7PTI3cYXNig60ojwzP7pR/k8dgEdy8/afEA0JAcwwiYlyOxEwfHw6Xl90SMuUV0vSkHT30RW8EZu93EN06fD51OrEDjOTPgQ3PjJBtUkauth5J9NjJrQqJlgGaMALKsLORAcKY36z8vwL0Df2rkfmwT+WrHVWegQps/RbwGLde+5jgBJFKcUW09fl80OMmyj0eGjplxArqqg4uDFw56+24Sra91TgxrgZQFqpnIuWQpxqsYrxUsDJ8k57wyn4nU1+ZAMQW76nv9kyl4pGYRpDFcUkeCqogNZVNI8Stki3XVBdXCMZQi8pocxgxuFFQda89JRano7xF94m/0c4ZPdwNXou7A32icV80y4aEXttdZtLdgANCi+2Zc4gJUo1gBnvMTSU1t8ANLtwmcvnztuqkh525kzRQHvUbFoYQaJWnjKOqq/MxmGWwb6BWK5gQkPMkT7/4loTOYQq0CC3I5CbOoAjzyp32zx+Sq5CM96dvulQvjx4WwjU092CfJ7GB2PgjhuYuX9cdp6FcbK2+MqLqmuMBRdjCchPK1epiWD926MJiZycKblOzG9Re4naRCts+xnJYck+8GvX32WdhicKAvChaWEyhThBebgqaNlk1ors7K2GRVLj8j89NR2M/9+YEi87rrTcz7aHbxSMorrNbJkkIKMA1+5MC0/oJ+ZpruTzcYnAEW+1R2Mz1DBqRr+REKJ8Vw== x-forefront-antispam-report: CIP:255.255.255.255;CTRY:;LANG:en;SCL:1;SRV:;IPV:NLI;SFV:NSPM;H:DBAP191MB1289.EURP191.PROD.OUTLOOK.COM;PTR:;CAT:NONE;SFS:(13230001)(4636009)(366004)(186003)(7696005)(44832011)(6916009)(38070700005)(66946007)(2906002)(26005)(122000001)(6506007)(9686003)(71200400001)(8936002)(91956017)(52536014)(33656002)(38100700002)(19627405001)(83380400001)(86362001)(55016003)(8676002)(498600001)(4326008)(5660300002)(76116006)(64756008)(66446008)(66476007)(66556008);DIR:OUT;SFP:1102; x-ms-exchange-antispam-messagedata-chunkcount: 1 x-ms-exchange-antispam-messagedata-0: =?iso-8859-1?Q?SxduMEdbBgRNeWjB7C8PAivIBTv5PEa6Y1J7+PdeGGi1s2pziz4KLmVAhA?= =?iso-8859-1?Q?KDXT0nWRCioKaA47VOwN8xi7SXuwxU24rfOc0AsN41EcA1ua2Tcb5e4IYi?= =?iso-8859-1?Q?iFlnIP3UeWMtEdfQXdwIjrUB3k8v5xqai/gDpBvD4raUuWauVgk3eDCtm3?= =?iso-8859-1?Q?kIEqCVX80eFGfdBv3Ec1CAwoD7Fxb8f/HAXpgNsxLIPERRfk8NuUJlTUpw?= =?iso-8859-1?Q?MYGr/NC0jedqPlaUYh8e4k5zx7nopQkXlHKSgt7o9iyIjuqjh0t4RZ8Efu?= =?iso-8859-1?Q?g5AeYfaymLUwqlkx9NHdpzEd+0hzg5EDFUS5mftsi6ZLgIbWgWuRjZrv8A?= =?iso-8859-1?Q?QGGpyiLDOfIyYvFp9QQNv4vLFHZ6Pl6zTy/Ja5YM8CvC+AKXCpgyjeBf7J?= =?iso-8859-1?Q?rUeXbuyf1Z4KdHDPzFdAf/a5u4K2X7I/fisJh/w8k/EmlLlLUzdSB+erBs?= =?iso-8859-1?Q?BATee6k3/HCU7kfuSbcqiHiFEEcJXEu+/16paubAUXtlMhrP3Vtvr8Q6Pu?= =?iso-8859-1?Q?n5odk/HlOZyjuIYaw+2N/quKb6BJ9wEdfm5W/I896Zo3T2ZA7M4+GOFSut?= =?iso-8859-1?Q?+mpqGiAX1cSFmNwcrofn3dDhPLUGiDf0zJ9mDoUZBywR1UzLMEUm827hO3?= =?iso-8859-1?Q?tfX7In81RURBp6FwLlYbNw4p8bNBoFvW0TNkIWt04yNG/7iY0gOHV/IXH8?= =?iso-8859-1?Q?56+QSmQ/W06KrJbS5r+YH03pgc2UlfJ/F+nogywCJ6ncA60AVo+NyScsx+?= =?iso-8859-1?Q?3/AFjPZdXcg8qx+Qd5rsUPcWT+sfuOfXbD9bgTDoitKbNVFbO20GYGfOOM?= =?iso-8859-1?Q?7VehbJBsJJoMWvzci//acifDiYiyQ8dH4OkSUjk0+SXU0hBKCn+ZDInyEu?= =?iso-8859-1?Q?tGn/cL28wV+lFQ+ZWKK2hSG8SzwN4ne6UqMORnLpl2wNMThgNfq/0uArWq?= =?iso-8859-1?Q?sQ99X4l56gOWp6LS4yi1cucRDMPUOMlEMdcdIJZZi2lrjIB3eX5mDTxq5S?= =?iso-8859-1?Q?aJZ4fSdluz9shbuZcWoyZkRWfBJNLm0QzCQZjIkZ3lFQLE4b/YpZeuuihZ?= =?iso-8859-1?Q?2sjGIVp2EisEpg8LbWRHr7GaUdL4F7nDXyD0KUUcbroR2vkYS50PIafJR/?= =?iso-8859-1?Q?t5J9vI5y/Qc8emb6/qZvKMCuvX4wDjap4ffh/KkBx4TyFNl+dsgNwjeYl3?= =?iso-8859-1?Q?wICplFwlGgqC9RGT95eTqo4/KDv6vxFnm95KlqD/n1VDHpTTSZI+8u3RN7?= =?iso-8859-1?Q?bSNTnLiqbSYAEcfQKZaj0gxH27iwRQJLesNYSX31PSYGfmfJBqT0f4ZHTP?= =?iso-8859-1?Q?I+eDm73jR6/3Bs1CsRzsFtqdo72P+fz+0TvKIKhmfpQF/awIs9yFMictYa?= =?iso-8859-1?Q?m4Xp/ZwgM/cMCtK6VQMClgw9XLCrwTFvgv8L3UQvKRS3q5nCm7ICAOsw/b?= =?iso-8859-1?Q?svgbWwTa39emT2szWvXhmTF9cxWJMZbVcPAoCBHNJShLc6U3ZK3leukBrV?= =?iso-8859-1?Q?RV2mTfQBeguIPbS4NveYDCREFRcsqb06bF+StPi+uDHfJedVVlz3pgVqG0?= =?iso-8859-1?Q?ilUkxnGLF0rl4Vc0iWAgQiMX57vI0GRZ5sfq2rMQ7TS8MQQtWjSkZzMQ/I?= =?iso-8859-1?Q?R1dud4oOAKXUcrT+vt/kKL5a0bfvKMfN3Y3SyNFcBoKKpezgmCqZDCzubi?= =?iso-8859-1?Q?MZERVyqDXGflA+Niuac353oGRo3N0v4sTJplVayCVxNFwlC2PVJ0Y5PLOZ?= =?iso-8859-1?Q?GGvA=3D=3D?= Content-Type: multipart/alternative; boundary="_000_DBAP191MB128960431DC469430A58F521B00A9DBAP191MB1289EURP_" MIME-Version: 1.0 X-MS-Exchange-CrossTenant-AuthAs: Internal X-MS-Exchange-CrossTenant-AuthSource: DBAP191MB1289.EURP191.PROD.OUTLOOK.COM X-MS-Exchange-CrossTenant-Network-Message-Id: 61784641-5dfb-4f5a-6afc-08da01a68386 X-MS-Exchange-CrossTenant-originalarrivaltime: 09 Mar 2022 08:26:30.5576 (UTC) X-MS-Exchange-CrossTenant-fromentityheader: Hosted X-MS-Exchange-CrossTenant-id: 75c696ec-5bfb-4892-9a0c-9187a9061cd6 X-MS-Exchange-CrossTenant-mailboxtype: HOSTED X-MS-Exchange-CrossTenant-userprincipalname: o8H4oCQdtGeQS3RpAYblw0tLcaZqF/AeeGmGLlO5apdxtJlUlgG7Y+MNn6jCvrPQZEHDeeZkb+Wx4zx7KesEMQ0Rvrym/ShOu9DvMv4qUaU= X-MS-Exchange-Transport-CrossTenantHeadersStamped: AS8P191MB1493 X-OriginatorOrg: 4js.com X-C2ProcessedOrg: 2f0c79df-a80b-40c2-9362-4c5fa2dae201 X-Proofpoint-GUID: iZaeF0zUYBQb2BC4cjXxxN8Bqvo8_GdY X-Proofpoint-ORIG-GUID: iZaeF0zUYBQb2BC4cjXxxN8Bqvo8_GdY X-Proofpoint-Virus-Version: vendor=baseguard engine=ICAP:2.0.205,Aquarius:18.0.816,Hydra:6.0.425,FMLib:17.11.64.514 definitions=2022-03-09_03,2022-03-04_01,2022-02-23_01 X-Proofpoint-Spam-Reason: safe List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --_000_DBAP191MB128960431DC469430A58F521B00A9DBAP191MB1289EURP_ Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hello! Using pg_temp as schema prefix for trigger function and sequence seems to d= o 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=3D0 THEN new.pk:=3Dls; ELSE IF new.pk>=3Dls 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) --_000_DBAP191MB128960431DC469430A58F521B00A9DBAP191MB1289EURP_ Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Hello!

Using pg_temp as schema prefix for trigger function and sequence seems to d= o 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=3D0 THEN
        new.pk:=3Dls;
     ELSE
        IF new.pk>=3Dls 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 PRO= CEDURE 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)


--_000_DBAP191MB128960431DC469430A58F521B00A9DBAP191MB1289EURP_--