Re: Best practice for naming temp table trigger functions

Please don't top-post.

On Tue, Mar 8, 2022 at 8:38 AM Sebastien Flaesch <sebastien(dot)flaesch(at)4js(dot)com>
wrote:

> The doc should describe that it's allowed to create triggers on temp
> tables:
> https://www.postgresql.org/docs/14/sql-createtrigger.html
>

A temporary table IS a table, so the fact this works on temporary tables is
unsurprising. There is nothing special about create trigger in this regard
that warrants a special mention of this fact. The trigger is created in
the same schema as the table is attached to, and is namespaced to the table
as well. Both of these facts are documented on that page, under the
description for the "name" parameter.

What is the best practice, to avoid such issues?
>

Don't define triggers on temporary tables. Or just write the function in
such a way that it can be attached to the triggers of any table - i.e.,
don't have one trigger function per trigger/table.

> But I would like to have that function dropped when the temp table is
> destroyed ...
>

Then you are back to constructing some kind of unique name. Though maybe,
and I've not tried this myself or done any research, you could schema
qualify the function using the pg_temp schema, and then make sure it
properly goes away when the session ends.

>
> Or, is there a way to define triggers directly with some anonymous code
> block?
>
>
Unfortunately that is not presently an option. It seems interesting but
frankly the motivation for that largely stems from a desire to add triggers
to temporary tables and usually that isn't a particularly efficient nor
easy to learn design choice. Putting the trigger logic into the same
routine as the temporary table and doing explicit bulk execution against
sets of rows instead of per-row triggers or even transition table sets is
going to be easier to read, modify, and likely run faster too. Plus you
reduce the amount of catalog churn to just the temporary table, not that
and a trigger and a function.

David J.