Thread: Getting user who fired a trigger

Dear All,
I have made a trigger procedure that logs all table's changes to the
log_table in XML format, but i do need to log a user who did that and
not succsessful with this.
The documentation (including "37.10. Trigger Procedures") give me nothing.
Could somene supply me with a solution or documentation part that i
should read?
My trigger function is written in PL/Ruby because i found no way to make
a "table's structure independent function" with PL/pgsql.

And one more question - is there any special type to store XML instead
of text? This question arised from the problem, that i'm planning (and i
do need this) to store all the database updation history and some small
procedures like adding a user will produce from 1 to 10 records in the
log table of text type. So updating of 1 byte will produce kilobytes of
text. So, maybe there is some compact type for XML storing? (i know that
i could pack it and store in zipped or other format, but i would like to
have a search possibility);

--
Best regads,
Anton Nikiforov

Anton Nikiforov wrote:
> Dear All,
> I have made a trigger procedure that logs all table's changes to the
> log_table in XML format, but i do need to log a user who did that and
> not succsessful with this.
> The documentation (including "37.10. Trigger Procedures") give me nothing.
> Could somene supply me with a solution or documentation part that i
> should read?

Can't remember which part it is, but you want one of:
   SELECT CURRENT_USER;
   SELECT SESSION_USER;

> My trigger function is written in PL/Ruby because i found no way to make
> a "table's structure independent function" with PL/pgsql.
>
> And one more question - is there any special type to store XML instead
> of text? This question arised from the problem, that i'm planning (and i
> do need this) to store all the database updation history and some small
> procedures like adding a user will produce from 1 to 10 records in the
> log table of text type. So updating of 1 byte will produce kilobytes of
> text. So, maybe there is some compact type for XML storing? (i know that
> i could pack it and store in zipped or other format, but i would like to
> have a search possibility);

Sorry, I don't think I understand. Do you want to store multiple
versions of the same XML document?
Or are you logging changes to non-XML data as XML text (and if so why)?

--
   Richard Huxton
   Archonet Ltd

Thanks for the replay, Richard.

Richard Huxton wrote:
> Anton Nikiforov wrote:
>
>> Dear All,
>> I have made a trigger procedure that logs all table's changes to the
>> log_table in XML format, but i do need to log a user who did that and
>> not succsessful with this.
>> The documentation (including "37.10. Trigger Procedures") give me
>> nothing.
>> Could somene supply me with a solution or documentation part that i
>> should read?
>
>
> Can't remember which part it is, but you want one of:
>   SELECT CURRENT_USER;
>   SELECT SESSION_USER;
test=# SELECT CURRENT_USER;
  current_user
--------------
  anton
(1 row)

test=# SELECT SESSION_USER;
  session_user
--------------
  anton
(1 row)

This gives the same output. Looks like i should check this with the real
operatins to understand the difference or RT*M more.

>
>> My trigger function is written in PL/Ruby because i found no way to
>> make a "table's structure independent function" with PL/pgsql.
>>
>> And one more question - is there any special type to store XML instead
>> of text? This question arised from the problem, that i'm planning (and
>> i do need this) to store all the database updation history and some
>> small procedures like adding a user will produce from 1 to 10 records
>> in the log table of text type. So updating of 1 byte will produce
>> kilobytes of text. So, maybe there is some compact type for XML
>> storing? (i know that i could pack it and store in zipped or other
>> format, but i would like to have a search possibility);
>
>
> Sorry, I don't think I understand. Do you want to store multiple
> versions of the same XML document?
> Or are you logging changes to non-XML data as XML text (and if so why)?
>
I'm storing XML prepared text in the database in the column of type
text. And the question is - is there some more wise way to do that? Like
column type XML maybe? :)

--
Best regads,
Anton Nikiforov

Anton Nikiforov wrote:
>>> Could somene supply me with a solution or documentation part that i
>>> should read?
>>
>>
>>
>> Can't remember which part it is, but you want one of:
>>   SELECT CURRENT_USER;
>>   SELECT SESSION_USER;
>
> test=# SELECT CURRENT_USER;
>  current_user
> --------------
>  anton
> (1 row)
>
> test=# SELECT SESSION_USER;
>  session_user
> --------------
>  anton
> (1 row)
>
> This gives the same output. Looks like i should check this with the real
> operatins to understand the difference or RT*M more.

Sometimes a function may be running with the permissions of its creator
rather than the user running it. See the CREATE FUNCTION reference for
details.

--
   Richard Huxton
   Archonet Ltd