Re: In which session context is a trigger run?

Hi!

On Fri, Dec 28, 2018 at 3:25 PM Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> wrote:
> > Sure, but why is a temporary function used as a temporary trigger made
>
> There is no such thing as a temporary trigger.

A trigger defined using a temporary function gets deleted once a
function gets deleted, which is at the end of the session. Thus, it is
a temporary trigger. Feel free to try it. Create a function in pg_temp
and then define a trigger, disconnect, and you will see that trigger
is deleted as well.

> > from my session not run inside my session? Then it could see a
> > temporary table made in my session.
>
> Except that is not what your OP stated:
>
> "And I add it to a regular table as a trigger:
>
> CREATE TRIGGER posts_insert AFTER INSERT ON posts REFERENCING NEW
> TABLE AS new_table FOR EACH STATEMENT EXECUTE FUNCTION
> pg_temp.my_function();
>
> When a row is added to "posts" table outside of my session, function
> "my_function" is called, but it seems it cannot access "posts_temp"
> table."
>
> So are you talking about another case now?

No. Still the same case. I have a regular table "posts" and a
temporary table "posts_temp". I want to create a trigger on "posts"
which calls "my_function". "my_function" then copies data from "posts"
to "posts_temp". The problem is that if "posts" is modified in another
session, the trigger cannot access "posts_temp". I wonder if there is
a way to call "my_function" inside the same temporary context /
session where it was defined, because in that same session also
"posts_temp" was defined.

Mitar