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--