Thread: Is it possible to get username information while writing trigger?
Hey:
What I want is to add a log entry at the last column of each row, which will record the history update, insert automatically when relative statement is processed.
I have read the documentation on triggers, which helps a lot. However, I may have few more extra requirement to complete my wishes:
1, I would like to get the username of who executed the statement;
2, I would like to get the column name that is being updated;
If it is possible and how should I do it??
Thanks a lot!
a wrote: > What I want is to add a log entry at the last column of each row, which will record the > history update, insert automatically when relative statement is processed. > > I have read the documentation on triggers, which helps a lot. However, I may have few > more extra requirement to complete my wishes: > > 1, I would like to get the username of who executed the statement; > > 2, I would like to get the column name that is being updated; > > If it is possible and how should I do it?? You could use the "current_user" function to get the current user. Mind, however, that updates caused by a cascading update from a foreign key constraint will be executed as the owner of the table, so it would be better to use "session_user" to avoid surprises. You cannot get the column name, because PostgreSQL updates a whole row, not an individual column. The best you can do is to check which column values are different in OLD and NEW. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Thank you for your reply;
Please allow me to ask few more questions:
1, Since I'm writing a C trigger function, is there any method for me to get some of the basic information like the follow:
(1) Total number of rows;
(2) Rows' names;
(3) Value of OLD and NEW;
2, Is there any possibility of passing the SQL statement it self into the trigger?
3, Is it possible for me to exam before statement trigger so that I would be able to loop it once and copying the update information to the rest of rows.
Thanks a lot!
------------------ Original ------------------
From: "Laurenz Albe";<laurenz.albe@cybertec.at>;
Send time: Monday, May 7, 2018 3:57 PM
To: "a"<372660931@qq.com>; "pgsql-general"<pgsql-general@postgresql.org>;
Subject: Re: Is it possible to get username information while writingtrigger?
> What I want is to add a log entry at the last column of each row, which will record the
> history update, insert automatically when relative statement is processed.
>
> I have read the documentation on triggers, which helps a lot. However, I may have few
> more extra requirement to complete my wishes:
>
> 1, I would like to get the username of who executed the statement;
>
> 2, I would like to get the column name that is being updated;
>
> If it is possible and how should I do it??
You could use the "current_user" function to get the current user.
Mind, however, that updates caused by a cascading update from a
foreign key constraint will be executed as the owner of the table,
so it would be better to use "session_user" to avoid surprises.
You cannot get the column name, because PostgreSQL updates a whole row,
not an individual column. The best you can do is to check which
column values are different in OLD and NEW.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
a wrote: > Please allow me to ask few more questions: > > 1, Since I'm writing a C trigger function, is there any method for me to get some of the > basic information like the follow: > > (1) Total number of rows; > (2) Rows' names; > (3) Value of OLD and NEW; > > 2, Is there any possibility of passing the SQL statement it self into the trigger? > > 3, Is it possible for me to exam before statement trigger so that I would be able to loop it > once and copying the update information to the rest of rows. I don't know if there is a reliable way to get the SQL statement from a C trigger. For the other things, perhaps a statement level trigger with transition relations can help. You can then access the transition relations from your C code with the tg_oldtable and tg_newtable tuplestores. (https://www.postgresql.org/docs/current/static/trigger-interface.html) Yours, Laurenz Albe -- +43-670-6056265 Cybertec Schönig & Schönig GmbH Gröhrmühlgasse 26, A-2700 Wiener Neustadt Web: https://www.cybertec-postgresql.com
On 05/07/2018 01:39 AM, a wrote: > Thank you for your reply; > > Please allow me to ask few more questions: > > 1, Since I'm writing a C trigger function, is there any method for me to > get some of the basic information like the follow: > > (1) Total number of rows; > (2) Rows' names; > (3) Value of OLD and NEW; > > 2, Is there any possibility of passing the SQL statement it self into > the trigger? > > 3, Is it possible for me to exam before statement trigger so that I > would be able to loop it once and copying the update information to the > rest of rows. Transition table(s): https://www.postgresql.org/docs/10/static/sql-createtrigger.html This is new to version 10 and I have not actually used this feature yet, so all I can do is point you at the docs. > > Thanks a lot! > > > ------------------ Original ------------------ > *From: * "Laurenz Albe";<laurenz.albe@cybertec.at>; > *Send time:* Monday, May 7, 2018 3:57 PM > *To:* "a"<372660931@qq.com>; "pgsql-general"<pgsql-general@postgresql.org>; > *Subject: * Re: Is it possible to get username information while > writingtrigger? > > a wrote: > > What I want is to add a log entry at the last column of each row, > which will record the > > history update, insert automatically when relative statement is > processed. > > > > I have read the documentation on triggers, which helps a lot. > However, I may have few > > more extra requirement to complete my wishes: > > > > 1, I would like to get the username of who executed the statement; > > > > 2, I would like to get the column name that is being updated; > > > > If it is possible and how should I do it?? > > You could use the "current_user" function to get the current user. > > Mind, however, that updates caused by a cascading update from a > foreign key constraint will be executed as the owner of the table, > so it would be better to use "session_user" to avoid surprises. > > You cannot get the column name, because PostgreSQL updates a whole row, > not an individual column. The best you can do is to check which > column values are different in OLD and NEW. > > Yours, > Laurenz Albe > -- > Cybertec | https://www.cybertec-postgresql.com -- Adrian Klaver adrian.klaver@aklaver.com