From arcpro@digitalwizardry.net Sat Feb  8 17:36:12 2003
X-Original-To: pgsql-sql@postgresql.org
Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id 68EB2475425
	for <pgsql-sql@postgresql.org>; Sat,  8 Feb 2003 12:36:12 -0500 (EST)
Received: from node3.mis.net (node3.mis.net [12.162.191.3])
	by postgresql.org (Postfix) with ESMTP id B9A534753A1
	for <pgsql-sql@postgresql.org>; Sat,  8 Feb 2003 12:36:11 -0500 (EST)
Received: from jrhome (modem-pool-1737.mis.net [206.28.44.117])
	by node3.mis.net (8.12.5/8.12.5) with SMTP id h18HYhDB013103
	for <pgsql-sql@postgresql.org>; Sat, 8 Feb 2003 12:34:44 -0500
Message-ID: <002101c2cf98$869a6840$752c1cce@jrhome>
From: "James C. Ousley" <arcpro@digitalwizardry.net>
To: <pgsql-sql@postgresql.org>
Subject: PL/Pgsql trigger function problem.
Date: Sat, 8 Feb 2003 12:35:49 -0500
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_NextPart_000_001C_01C2CF6E.9C6397C0"
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 6.00.2600.0000
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000
X-MailScanner: Found to be clean
X-MailScanner-SpamScore: s
X-Virus-Scanned: by AMaViS new-20020517
X-Archive-Number: 200302/203
X-Sequence-Number: 11601

This is a multi-part message in MIME format.

------=_NextPart_000_001C_01C2CF6E.9C6397C0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

  This is suppose to pull all the columns of the table that initiated the t=
rigger func from the sys catalogs, loop through them and put everything tha=
t has changed between OLD and NEW into a comma delimited string for input i=
nto a log like table for future analysis via middleware (php,perl..,etc).  =
Here is the problem, OLD.A results in 'old does not have field A', which is=
 true. I cant get the OLD and NEW record objects to realize that I want OLD=
.<string value of A> for the column name instead of an explicit A as the co=
lumn name.  The only way I can find to make this work is by using TCL for t=
he procedural language because of the way it casts the OLD and NEW into an =
associative array instead of a RECORD object, but by using TCL I will lose =
functionallity in the "complete" version of the following function which ha=
s been stripped to show my specific problem so using TCL is currently not i=
n my list of options.  Any insight will be greatly appreciated.

create or replace function hmm() returns TRIGGER as '
DECLARE
table_cols RECORD;
attribs VARCHAR;
A VARCHAR;
BEGIN
IF TG_OP =3D ''UPDATE'' THEN
     FOR table_cols IN select attname from pg_attribute where attrelid =3D =
TG_RELID and attnum > -1 LOOP
      A :=3D table_cols.attname;
      IF OLD.A !=3D NEW.A THEN  --Begin problem=20
       IF attribs !=3D '''' THEN
       attribs :=3D attribs || '','' || table_cols.attname || ''=3D'' || OL=
D.A || ''->'' || NEW.A;
       ELSE
       attribs :=3D table_cols.attname || ''=3D'' || OLD.A || ''->'' || NEW=
.A;
       END IF;
      END IF;
     END LOOP;
END IF;
RAISE EXCEPTION ''%'', attribs;
RETURN NULL;
END;
' Language 'plpgsql';

------=_NextPart_000_001C_01C2CF6E.9C6397C0
Content-Type: text/html;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.2715.400" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV>
<DIV><FONT size=3D2>&nbsp; This is suppose to pull all the columns of the t=
able=20
that initiated the trigger func from the sys catalogs, loop through them an=
d put=20
everything that has changed between OLD and NEW into a comma delimited stri=
ng=20
for input into a log like table for future analysis via middleware=20
(php,perl..,etc).&nbsp; Here is the problem, OLD.A results in 'old does not=
 have=20
field A', which is true. I cant get the OLD and NEW&nbsp;record objects to=
=20
realize that I want OLD.&lt;string value of A&gt; for the column=20
name&nbsp;instead of an explicit A as the column name.&nbsp; The only way I=
 can=20
find to make this work is by using TCL for the procedural language because =
of=20
the way it casts the OLD and NEW into an associative array instead of a REC=
ORD=20
object, but by using TCL I will lose functionallity in the "complete" versi=
on of=20
the following function which has been stripped to show my specific problem=
=20
so&nbsp;using TCL&nbsp;is currently not in my list of options.&nbsp; Any in=
sight=20
will be greatly appreciated.<BR></FONT>
<DIV><FONT size=3D2>create or replace function hmm() returns TRIGGER as=20
'<BR>DECLARE<BR>table_cols RECORD;<BR>attribs VARCHAR;<BR>A=20
VARCHAR;<BR>BEGIN<BR>IF TG_OP =3D ''UPDATE'' THEN<BR>&nbsp;&nbsp;&nbsp;&nbs=
p; FOR=20
table_cols IN select attname from pg_attribute where attrelid =3D TG_RELID =
and=20
attnum &gt; -1 LOOP<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; A :=3D=20
table_cols.attname;<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; IF OLD.A !=3D NEW.A=
=20
THEN&nbsp; --Begin problem</FONT> </DIV>
<DIV><FONT size=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; IF attribs !=3D ''=
''=20
THEN<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; attribs :=3D attribs || '','' =
||=20
table_cols.attname || ''=3D'' || OLD.A || ''-&gt;'' ||=20
NEW.A;<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
ELSE<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; attribs :=3D table_cols.attnam=
e ||=20
''=3D'' || OLD.A || ''-&gt;'' || NEW.A;<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&n=
bsp;=20
END IF;<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; END IF;<BR>&nbsp;&nbsp;&nbsp;&nbs=
p;=20
END LOOP;<BR>END IF;<BR>RAISE EXCEPTION ''%'', attribs;<BR>RETURN=20
NULL;<BR>END;<BR>' Language 'plpgsql';</FONT></DIV></DIV></DIV></BODY></HTM=
L>

------=_NextPart_000_001C_01C2CF6E.9C6397C0--




From ch@rodos.fzk.de Wed Feb  12 13:49:00 2003
X-Original-To: pgsql-sql@postgresql.org
Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id 4272F475A80
	for <pgsql-sql@postgresql.org>; Wed, 12 Feb 2003 08:49:00 -0500 (EST)
Received: from fzkmail2.fzk.de (fzkmail2.fzk.de [141.52.27.52])
	by postgresql.org (Postfix) with SMTP id 1A7F64759BD
	for <pgsql-sql@postgresql.org>; Wed, 12 Feb 2003 08:48:59 -0500 (EST)
Received: FROM resy5.fzk.de BY fzkmail2.fzk.de ;
 Wed Feb 12 14:49:00 2003 +0100
Received: œby rodos.fzk.de with ESMTP (8.8.6 (PHNE_17135)/8.7.1) id OAA21235; Wed, 12 Feb 2003 14:48:59 +0100 (MET)
Message-ID: <3E4A50CA.1295488@rodos.fzk.de>
Date: Wed, 12 Feb 2003 14:48:58 +0100
From: Christoph Haller <ch@rodos.fzk.de>
X-Mailer: Mozilla 4.77 [en] (X11; U; HP-UX B.10.20 9000/780)
X-Accept-Language: en
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Cc: arcpro@digitalwizardry.net
Subject: Re: PL/Pgsql trigger function problem.
Content-Type: text/plain; charset=iso-8859-2
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: by AMaViS new-20020517
X-Archive-Number: 200302/238
X-Sequence-Number: 11636

>
>   This is suppose to pull all the columns of the table that initiated
the t=
> rigger func from the sys catalogs, loop through them and put
everything tha=
> t has changed between OLD and NEW into a comma delimited string for
input i=
> nto a log like table for future analysis via middleware
(php,perl..,etc).  =
> Here is the problem, OLD.A results in 'old does not have field A',
which is=
>  true. I cant get the OLD and NEW record objects to realize that I
want OLD=
> .<string value of A> for the column name instead of an explicit A as
the co=
> lumn name.  The only way I can find to make this work is by using TCL
for t=
> he procedural language because of the way it casts the OLD and NEW
into an =
> associative array instead of a RECORD object, but by using TCL I will
lose =
> functionallity in the "complete" version of the following function
which ha=
> s been stripped to show my specific problem so using TCL is currently
not i=
> n my list of options.  Any insight will be greatly appreciated.
>
> create or replace function hmm() returns TRIGGER as '
> DECLARE
> table_cols RECORD;
> attribs VARCHAR;
> A VARCHAR;
> BEGIN
> IF TG_OP =3D ''UPDATE'' THEN
>      FOR table_cols IN select attname from pg_attribute where attrelid
=3D =
> TG_RELID and attnum > -1 LOOP
>       A :=3D table_cols.attname;
>       IF OLD.A !=3D NEW.A THEN  --Begin problem=20
>        IF attribs !=3D '''' THEN
>        attribs :=3D attribs || '','' || table_cols.attname || ''=3D''
|| OL=
> D.A || ''->'' || NEW.A;
>        ELSE
>        attribs :=3D table_cols.attname || ''=3D'' || OLD.A || ''->''
|| NEW=
> .A;
>        END IF;
>       END IF;
>      END LOOP;
> END IF;
> RAISE EXCEPTION ''%'', attribs;
> RETURN NULL;
> END;
> ' Language 'plpgsql';
>

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





From arcpro@digitalwizardry.net Wed Feb  12 14:55:32 2003
X-Original-To: pgsql-sql@postgresql.org
Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id 7FEEB474E5C
	for <pgsql-sql@postgresql.org>; Wed, 12 Feb 2003 09:55:32 -0500 (EST)
Received: from exch1.mis.net (exch1.mis.net [204.68.227.117])
	by postgresql.org (Postfix) with ESMTP id DE8EF474E44
	for <pgsql-sql@postgresql.org>; Wed, 12 Feb 2003 09:55:31 -0500 (EST)
Received: from digitalwizardry.net ([208.135.168.41]) by exch1.mis.net with
	Microsoft SMTPSVC(5.0.2195.5329); Wed, 12 Feb 2003 09:44:56 -0500
Message-ID: <3E4A616E.1090405@digitalwizardry.net>
Date: Wed, 12 Feb 2003 09:59:58 -0500
From: "Jr." <arcpro@digitalwizardry.net>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US;
	rv:1.0.2) Gecko/20021120 Netscape/7.01
X-Accept-Language: en-us, en
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: Re: PL/Pgsql trigger function problem.
References: <3E4A50CA.1295488@rodos.fzk.de>
Content-Type: text/plain; charset=ISO-8859-2; format=flowed
Content-Transfer-Encoding: 7bit
X-OriginalArrivalTime: 12 Feb 2003 14:44:56.0187 (UTC)
	FILETIME=[4F4FECB0:01C2D2A5]
X-Virus-Scanned: by AMaViS new-20020517
X-Archive-Number: 200302/241
X-Sequence-Number: 11639

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>.net
 766682 | John Smith   | email     |     134463 | 2003-02-11 
00:25:18.964397-05 | UPDATE | CT-Notes
 766686 | Kris Smith     | customers |            | 2003-02-11 
00:49:34.156166-05 | SEARCH | username c <username> and lname c
 766687 | Darren Smith   | customers |            | 2003-02-11 
00:54:56.762122-05 | SEARCH | username c <username> and lname c
 766688 | Kris Smith     | email     |     134463 | 2003-02-11 
00:57:31.345123-05 | UPDATE | CT-Notes
 766689 | Darren Smith   | email     |     130537 | 2003-02-11 
00:59:42.194031-05 | UPDATE | CT-Notes
 766707 | 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>.net
 766710 | 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
>
>
>
>
>  
>