Re: History: Write changes to an extra table

#1

begin Thomas Guettler schrieb:

Quote:

Hi,

Hi Thomas, how are you?

Quote:

I wrote a trigger which writes all changes in a table to a
history table (user, time, table, column, oldvalue, newvalue).

It works good, but here is one problem:

Just for info: Andreas 'ads' Scherbaum works on a project called
'tablelog':

http://ads.wars-nicht.de/blog/archiv...-tablelog.html

Quote:

I use one database user, and the application has its own
user management. Of course the history table should contain
the userid of my application, since I want to know who did the
changes.

How can I get the current application-user id into my trigger?

Could this be done with a temporary table? Or can you set session
variables, which can be accessed inside the trigger?

Maybe a solution:

define a custom variable in your postgresql.conf like this:

#custom_variable_classes = '' # list of custom variable class names
custom_variable_classes = 'myvar'
myvar.benutzer = 'foo'

Reload your configuration, now you can do this:

test=*# set myvar.benutzer = 'foobarbatz';
SET
test=*# select current_setting('myvar.benutzer');
current_setting
-----------------
foobarbatz
(1 row)

Hope that helps.

end
Andreas
--
Andreas Kretschmer
Linux - weil ich es mir wert bin!
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net

#2

Andreas Kretschmer schrieb:

Quote:

begin Thomas Guettler schrieb:
Hi,

Hi Thomas, how are you?

Nice to "see" you Andreas!

BTW, we can meet at http://www.linux-info-tag.de/ (Sat 3. Nov)
I am going to hold one workshop (python programming (for beginners))
and one talk about django (python web framework (which I use with
postgres)).

I hope we can meat us there.

Quote:

Maybe a solution:

define a custom variable in your postgresql.conf like this:

#custom_variable_classes = '' # list of custom variable class names
custom_variable_classes = 'myvar'
myvar.benutzer = 'foo'

Reload your configuration, now you can do this:

test=*# set myvar.benutzer = 'foobarbatz';
SET
test=*# select current_setting('myvar.benutzer');
current_setting
-----------------
foobarbatz
(1 row)

Thank you very much. This is what I was looking for.

Thomas

#3

begin Thomas Guettler <guettli (AT) thomas-guettler (DOT) de> wrote:

Quote:

Andreas Kretschmer schrieb:
begin Thomas Guettler schrieb:

BTW, we can meet at http://www.linux-info-tag.de/ (Sat 3. Nov)
I am going to hold one workshop (python programming (for beginners))
and one talk about django (python web framework (which I use with
postgres)).

I hope we can meat us there.

I think, we meet us there ;-)

Quote:

Maybe a solution:

Thank you very much. This is what I was looking for.

Glad to help you.

end
Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Re: History: Write changes to an extra table

comp.databases.postgresql comp.databases.postgresql

Re: History: Write changes to an extra table - 10-02-2007 , 01:07 AM

Re: History: Write changes to an extra table - 10-02-2007 , 02:31 PM

Re: History: Write changes to an extra table - 10-03-2007 , 02:29 AM