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.klaver@aklaver.com