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 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
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
--
http://mitar.tnode.com/
https://twitter.com/mitar_m
