Re: Is it possible to get username information while writingtrigger?

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