Is it possible to get username information while writing trigger?

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?

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

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