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
Thread-Topic: Best practice for naming temp table trigger functions
Thread-Index: AQHYMk3wKpd6RixG7ki7DRsQNFeEB6y1nug6gAAMJYCAAAt0IQ==
Date: Tue, 8 Mar 2022 17:24:18 +0000
Message-ID: 
 <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>
In-Reply-To: 
 <CAKFQuwZNZzgpo+P8bF6HG=VMq9ZZaw2kpvpgq1KaaL59NFVUTw@mail.gmail.com>
Accept-Language: en-US
Content-Language: en-US
suggested_attachment_session_id: f6ce1040-8dbd-c3c3-ca16-2518a285dc13
x-ms-publictraffictype: Email
x-ms-office365-filtering-correlation-id: b2d3f97c-30b2-487f-a51a-08da01287a60
x-ms-traffictypediagnostic: AM7P191MB0789:EE_
x-microsoft-antispam-prvs: 
 <AM7P191MB07891B691D092EABCD97EE1BB0099@AM7P191MB0789.EURP191.PROD.OUTLOOK.COM>
x-ms-exchange-senderadcheck: 1
x-ms-exchange-antispam-relay: 0
x-microsoft-antispam: BCL:0;
x-microsoft-antispam-message-info: 
 fMET5/QQB289AoYxj+ROPhUvYo+2OO72dkv/NmtLJloLnQLVS6dNPUZXP/iilKkaFedt2WHx2Dc83AXZNdOBv7oqe6+hxHsGumk+uHAPw2XGK5K/6OWXqc0J0IHewt+gVoBj4CKDXP3ocr2cJa6xc0urwyu8yNUWOz5QVGS4ysVpeL4oosCkQYKkmEEw/1f4UxgazcwrlLxFtfe8HZv/3jRyPg0U+7FnPSuwHtoUVWWtVHNjTBnuH3RmhKxmpBDScI6Xpd/tXotDBHtwIR4egpPmiQ8YYHFs9/tZ195LLahY55dpzvuVriV3BmV+SETJCr9RNsNqFtT3yHaaPCerhWvVlKL6nBDUviN1+k3WwZraAp3t41QARzOuAL7qIoHRSFWADkIKg66aL9JPhlP3J3MEcGfsNnOzi7lt41qjBSMuZ7g/uUVPvUkAQFTb3ZgYRwbZeDlyZNAVmjhHQ78whhbbKDtyyzpt35nqmErrW8C1+gaMmNBXfhFGwNa1uJvWR4XBlXKWgJ/c2MlzJVBaNJqmkRhKJqqT0ZFKloWM8PF0k2XWGxnFNfaVXmBMEppg8pmZR5QBuFnVmaE8jzJ9O5IdAKA7aPMGjwK7RAvT2uEz3sBEu4VhwFS8btcizqVSfn5KKsBkhg8tQWbXGnvQdDWiGv3BvdjtUdXEgDSFW7/eHq6S+Y7F6r8P1VzvY3jCnl2gwrJKMa6pzrpR9f5XKA==
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)(7696005)(64756008)(66476007)(316002)(8676002)(66446008)(66946007)(66556008)(76116006)(4326008)(91956017)(6506007)(4744005)(44832011)(2906002)(5660300002)(26005)(186003)(33656002)(9686003)(8936002)(52536014)(38100700002)(86362001)(508600001)(38070700005)(19627405001)(71200400001)(55016003)(6916009)(122000001);DIR:OUT;SFP:1102;
x-ms-exchange-antispam-messagedata-chunkcount: 1
x-ms-exchange-antispam-messagedata-0: 
 =?iso-8859-1?Q?xxX5kKhh2YYPwkGjtjiBYE60EO/3BrlihHDEwaM6K3QIaPLEzp9BY2DKDo?=
 =?iso-8859-1?Q?DbppmnFfuiGAN3FdS3f/0VHKfB18M0GWFKfCk2jURM8G8W1CbOzJlSa4b+?=
 =?iso-8859-1?Q?WHEABQpv0Pf5eIZ0ccLmuNt2MDOtHFWisUK/iaS7Lq5IkJqlLz26O7ge+B?=
 =?iso-8859-1?Q?yfA8uJgeXdNIfhvbwGeJ/Qoz3zboPPZlZ5I2QI5CN3gMUAlE13MDeU2+wN?=
 =?iso-8859-1?Q?ZR+HaqQ8w5F1nAPaO6HfgqRccvk/CgT2l1bK0gNpCQMTo8ph6seeRKTGtj?=
 =?iso-8859-1?Q?TwHunmV8tC2KGmjp8HFsHZ/5RSZa9qsZVfouMb/nl3fOxHHGWk9JskFeX4?=
 =?iso-8859-1?Q?edGrAlDCZYLU3a96qJUb+LuGlNbV3aARcx6p+aJmo1AiRI9XtFGBF6j4lK?=
 =?iso-8859-1?Q?dM1l34u3tPQ3HEQ9ZpzzJmOJGOgNY+VTY29INOZhuU3yw0ELe5vFDmb7vL?=
 =?iso-8859-1?Q?irPkkn32mIXwfnq1j/5YXv7OBI8OWRT/0Su7vlvVtv6Th6yG70UjIx2ydD?=
 =?iso-8859-1?Q?rxDRVLZY7fuBHRfVPF9aP5oFSwyVd3mjh2HhdM393l1LvHhMNWsGwAaR+V?=
 =?iso-8859-1?Q?iqCjXyBVSUYRw4VJ2dsWDdxLHUcRM1iW5dwnG5by2U8ATVVsS2R1oPLoq5?=
 =?iso-8859-1?Q?oHuZ5Nrt9BAMQXeMF36Lkt8sR07D5XK93g/qvldXHR/crSDDgwEVfu7xAk?=
 =?iso-8859-1?Q?z3qVSCjd+2XJa62eENjVsMuC9ykee5IhgK+RDg4d0HU5IZ53gEYf9OEdHx?=
 =?iso-8859-1?Q?GuDl6oW1R1ar6n4iuftsAnFk2WgQUCNhGZVaicpOd2VKPA4UgNhz+7IwZr?=
 =?iso-8859-1?Q?Mk0b4s4Egn7kInjFHOpVhH6H3UbMLgSOUo42hau/D17M1RyRnnpXBEDfdg?=
 =?iso-8859-1?Q?Y7iioIWyt8Av4k2c8R9+FLS/HOezg+GUzLYxQ6JVlWtxX2DIKoyJ6xEgK7?=
 =?iso-8859-1?Q?qBgF+vN/seypevxEGc+LizErxvhdOqIJUfrHw7tvFOu/h+9wjEwkZMPS4U?=
 =?iso-8859-1?Q?Pvz2HfIAz0mzL6pyTxAGwpmvQyYqyXEgpLLUQXzIpNOL9SaakFzQMTzoi6?=
 =?iso-8859-1?Q?67xEy4ok3lPY03oBVJyR7OQ1iHPg95G4/1dUaGnnf24vft83Iww4OX0YP/?=
 =?iso-8859-1?Q?37cOyP7m6z9r8KvALSrQ59+wE/NPa985Kc6UVsY5NrsdIEEKwKDnMKzIgX?=
 =?iso-8859-1?Q?Ia3wK9+IuOaNPSKWw5vNqeGTSE6Y1jbRFRRfnEyzeaQdvTxSdr0CU49uMc?=
 =?iso-8859-1?Q?9INac1PEvAjyNfIloHQyUxZO3D5SLAmeD3axRT+MjscPlIElX1r6J90r6w?=
 =?iso-8859-1?Q?Ix8/g2bPDnj0RNE3WdQaasQgEYu+7h6AbGZSGie0KpdjkT+HJC/OPWwm3j?=
 =?iso-8859-1?Q?k1VVMbOOs20jc+NZbNW7mTwGXrav8MXowIxE6R5HcwleQLsDLqdzpL3z37?=
 =?iso-8859-1?Q?NdVbdA1NB4C0rwNlChmPvlKhtV+RcPpbOE8mXnR/fhJlINae2fUf/7TSKe?=
 =?iso-8859-1?Q?WgFcagfFiqWmNrFEAfmyDVu2JOsqgBDv1WSzi6tU6ty6kGfqZ4bNGagZX2?=
 =?iso-8859-1?Q?wnn+JspUwTRZS6pGBHdfv7Kl3twk8zlrg7+JXEDS7VCadOEEG+ENoAwd90?=
 =?iso-8859-1?Q?t9vOr4DLuje9cq8ggoz+EgmOJ1hn3CkqOkTI/DCs1a7J2lGpKbmnki0AlW?=
 =?iso-8859-1?Q?u6C5iNvuWGMufvu4uBNmt/ZxfpkhLP+F2HlFNq41SxVgzwR0CSaSkfxDIY?=
 =?iso-8859-1?Q?9kuQ=3D=3D?=
Content-Type: multipart/alternative;
	boundary="_000_DBAP191MB12891233EFFBA6A4CC30E420B0099DBAP191MB1289EURP_"
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: 
 b2d3f97c-30b2-487f-a51a-08da01287a60
X-MS-Exchange-CrossTenant-originalarrivaltime: 08 Mar 2022 17:24:18.9195
 (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: 
 TLibJH8by1COT7syafY5XEpp8l2QmabVMY56gnOhx01UiiVGvTvmfjG+GmweYyJz+WfusjjI4Vh5i8Q8WbZVxfIoHaoAz3Vb9V/IBzTAyRs=
X-MS-Exchange-Transport-CrossTenantHeadersStamped: AM7P191MB0789
X-OriginatorOrg: 4js.com
X-C2ProcessedOrg: 2f0c79df-a80b-40c2-9362-4c5fa2dae201
X-Proofpoint-GUID: DKOHxXWMUL16rSoYrb0SS1yjnOIP-BfA
X-Proofpoint-ORIG-GUID: DKOHxXWMUL16rSoYrb0SS1yjnOIP-BfA
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-08_06,2022-03-04_01,2022-02-23_01
X-Proofpoint-Spam-Reason: safe
List-Id: <pgsql-sql.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-sql@lists.postgresql.org>
List-Owner: <mailto:pgsql-sql-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-sql>
Archived-At: 
 <https://www.postgresql.org/message-id/DBAP191MB12891233EFFBA6A4CC30E420B0099%40DBAP191MB1289.EURP191.PROD.OUTLOOK.COM>
Precedence: bulk

--_000_DBAP191MB12891233EFFBA6A4CC30E420B0099DBAP191MB1289EURP_
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable


Would it make sense (seems possible!) to create the trigger function in the=
 pg_my_temp_schema() schema ?

create function pg_temp_4.TT1_SRL() returns trigger ...

Same question for sequences... (used in my triggers)

Background: All this stuff is to mimic Informix-style SERIALs.

If PostgreSQL native serial would have options to behave like Informix seri=
al, we would not need triggers on our tables.

With Informix serial/bigserial:

  *   INSERT with zero value specified for serial column generates a new se=
rial value from underlying sequence.
  *   INSERT with non-zero value specified for serial column sets that valu=
e and automatically increments the underlying sequence.

Seb


--_000_DBAP191MB12891233EFFBA6A4CC30E420B0099DBAP191MB1289EURP_
Content-Type: text/html; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<html>
<head>
<meta http-equiv=3D"Content-Type" content=3D"text/html; charset=3Diso-8859-=
1">
<style type=3D"text/css" style=3D"display:none;"> P {margin-top:0;margin-bo=
ttom:0;} </style>
</head>
<body dir=3D"ltr">
<div style=3D"font-family: Calibri, Arial, Helvetica, sans-serif; font-size=
: 12pt; color: rgb(0, 0, 0);">
<br>
</div>
<div style=3D"font-family: Calibri, Arial, Helvetica, sans-serif; font-size=
: 12pt; color: rgb(0, 0, 0);">
Would it make sense (seems possible!) to create the trigger function in the=
 pg_my_temp_schema() schema ?</div>
<div style=3D"font-family: Calibri, Arial, Helvetica, sans-serif; font-size=
: 12pt; color: rgb(0, 0, 0);">
<br>
</div>
<div style=3D"font-family: Calibri, Arial, Helvetica, sans-serif; font-size=
: 12pt; color: rgb(0, 0, 0);">
create function pg_temp_4.TT1_SRL() returns trigger ...<br>
</div>
<div style=3D"font-family: Calibri, Arial, Helvetica, sans-serif; font-size=
: 12pt; color: rgb(0, 0, 0);">
<br>
</div>
<div style=3D"font-family: Calibri, Arial, Helvetica, sans-serif; font-size=
: 12pt; color: rgb(0, 0, 0);">
Same question for sequences... (used in my triggers) <br>
</div>
<div style=3D"font-family: Calibri, Arial, Helvetica, sans-serif; font-size=
: 12pt; color: rgb(0, 0, 0);">
<br>
</div>
<div style=3D"font-family: Calibri, Arial, Helvetica, sans-serif; font-size=
: 12pt; color: rgb(0, 0, 0);">
Background: All this stuff is to mimic Informix-style SERIALs.</div>
<div style=3D"font-family: Calibri, Arial, Helvetica, sans-serif; font-size=
: 12pt; color: rgb(0, 0, 0);">
<br>
</div>
<div style=3D"font-family: Calibri, Arial, Helvetica, sans-serif; font-size=
: 12pt; color: rgb(0, 0, 0);">
If PostgreSQL native serial would have options to behave like Informix seri=
al, we would not need triggers on our tables.</div>
<div style=3D"font-family: Calibri, Arial, Helvetica, sans-serif; font-size=
: 12pt; color: rgb(0, 0, 0);">
<br>
</div>
<div style=3D"font-family: Calibri, Arial, Helvetica, sans-serif; font-size=
: 12pt; color: rgb(0, 0, 0);">
With Informix serial/bigserial:<br>
</div>
<div style=3D"font-family: Calibri, Arial, Helvetica, sans-serif; font-size=
: 12pt; color: rgb(0, 0, 0);">
<ul>
<li><span>INSERT with zero value specified for serial column generates a ne=
w serial value from underlying sequence.</span></li><li><span>INSERT with n=
on-zero value specified for serial column sets that value and automatically=
 increments the underlying sequence.</span></li></ul>
</div>
<div style=3D"font-family: Calibri, Arial, Helvetica, sans-serif; font-size=
: 12pt; color: rgb(0, 0, 0);">
Seb<br>
</div>
<div id=3D"appendonsend"></div>
<br>
</body>
</html>

--_000_DBAP191MB12891233EFFBA6A4CC30E420B0099DBAP191MB1289EURP_--