Re: PL/Pgsql trigger function problem.
От | Jr. |
---|---|
Тема | Re: PL/Pgsql trigger function problem. |
Дата | |
Msg-id | 3E4A616E.1090405@digitalwizardry.net обсуждение исходный текст |
Ответ на | Re: PL/Pgsql trigger function problem. (Christoph Haller <ch@rodos.fzk.de>) |
Список | pgsql-sql |
Christopher Your interpretation of my context is correct. I am using postgresql as the data warehouse for a user management interface (which utilizes perl and php) that is tracking everything from customer email accounts to web hosting. Using multiple logins based off the users real name via a staff table, ex userid of jsmith has a database user of "John Smith". By doing this, in conjunction with my current pl/pgsql function I am able to create a "touchlog" to track what each users do to the various account information tables with one function and triggers on the tables that wish tracking to be implemented. I am, as my code showed, attempting to make it more dynamic by using the system catalogs to retrieve column names. This way I can maximize the scalability of the function and do checks of what was changed (or added without recoding the function when a new service is added) and log it (which I am currently hard coding certain checks for billable actions but would want all of the changes logged in an ideal environment). And again with the way that TCL casts the OLD and NEW into an array I think I could probably accomplish it with TCL. But if my interpretation of the docs are correct by using TCL I may loose some functionality that I am currently using. I am porting my pl/pgsql function to TCL to see exactly where it may lead, I will post back my results of that experiment at a later time. Below I have included an example output of the log table so you can see what I am doing (note: the searches are added via middle ware. And there is another 'action' that I have to add into the middleware which is OPEN, because sometimes people do searches but never do anything else, and I need to know if they look at any account that was found in their search results. CT-Notes means they added a note bound to that account but the account did not require a modification, ie someone forgot their password and we had to look it up, verify who they were and give it back to them). I think that about sums it up. Thanks for the point of view as it may very well be the way I may have to do it. uac=# select * from touchlog where modtime between '2003-02-11' and '2003-02-12'; id | username | tablename | recid | modtime | action | attributes --------+-----------------+-----------+------------+-------------------------------+--------+--------------------------------------------------------------------766681 |John Smith | customers | | 2003-02-11 00:21:38.608865-05 | SEARCH | username e <username> and domain c <domain>.net766682 | John Smith | email | 134463 | 2003-02-11 00:25:18.964397-05 | UPDATE | CT-Notes766686 | Kris Smith | customers | | 2003-02-11 00:49:34.156166-05 | SEARCH | username c <username> and lname c766687 | Darren Smith | customers | | 2003-02-11 00:54:56.762122-05 | SEARCH | username c <username> and lname c766688 | Kris Smith | email | 134463 | 2003-02-11 00:57:31.345123-05 | UPDATE | CT-Notes766689 | Darren Smith | email | 130537 | 2003-02-11 00:59:42.194031-05 | UPDATE | CT-Notes766707 | Freda Smith | dsl | 3240 | 2003-02-11 07:40:38.071053-05 | INSERT |766708 | Freda Smith | email | 84241 | 2003-02-11 07:41:06.038679-05 | UPDATE | plan+Plan A->PLAN_DSL_768+Customer Name+<domain>.net766710 | Freda Smith | dialup | 127415 | 2003-02-11 07:41:18.170302-05 | DELETE | 1020772+<username>+<domain>.net+<pop location> >James, >If I understand your intentions correctly, you are trying to achieve a >general procedure >to log all updates of all tables. Right? >The only way I can think of from my point of knowledge is use middleware >to generate >a big sql script with a CREATE PROCEDURE and CREATE TRIGGER statement >for every table you want updates being logged. This might be no option >for you as well, >but I would like to hear if at least my interpretation of your request >was correct. >Regards, Christoph > > > > > >
В списке pgsql-sql по дате отправления: