Re: In which session context is a trigger run?

On 12/28/18 12:47 PM, Mitar wrote:
> Hi!
>
> It seems to me that triggers on a table are run in the session context
> of the user who made a change in the table, but not of the user who
> defined the trigger?
>
> So I create a temporary function:
>
> CREATE OR REPLACE FUNCTION pg_temp.my_function()

And it does what?

>
> And a temporary table:
>
> CREATE TEMPORARY TABLE posts_temp (
> "id" CHAR(17) PRIMARY KEY,
> );
>
> 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. It seems that "my_function" is called in the session of the

Temp tables can not be seen by other sessions.

> user who modified the table, and not the user who created the function
> (pg_temp.my_function above) or the user who added the trigger.
>
> Is there a way to modify/configure this? Switch the session? Is there
> some other way that I cold define a trigger which runs for a time of
> my session and runs and interact with temporary objects inside of my
> session, when data on regular tables is modified?
>
>
> Mitar
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com