From: | Mitar <mmitar(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | In which session context is a trigger run? |
Date: | 2018-12-28 20:47:58 |
Message-ID: | CAKLmikO=2kTnVpR2sjr-hTW6BW8pZwRhynVL=QZ5LnhO4jXEiw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Adrian Klaver | 2018-12-28 20:57:15 | Re: In which session context is a trigger run? |
Previous Message | Igor Korot | 2018-12-28 13:41:16 | Re: libpq bug? |