Received: from localhost.localdomain (postgresql.org [64.49.215.8]) by localhost (Postfix) with ESMTP id 49CCC4758EC for ; Thu, 16 May 2002 14:33:24 -0400 (EDT) Received: from wilma.planetcad.com (unknown [63.121.249.198]) by postgresql.org (Postfix) with ESMTP id B1C8647596E for ; Thu, 16 May 2002 14:33:21 -0400 (EDT) Received: from pcadntexch01.planetcad.com (pcadntexch01.co.planetcad.com [172.20.1.25]) by wilma.planetcad.com (8.11.2/8.9.3) with ESMTP id g4GCSRj29189 for ; Thu, 16 May 2002 06:28:27 -0600 Received: by pcadntexch01.planetcad.com with Internet Mail Service (5.5.2653.19) id ; Thu, 16 May 2002 12:33:16 -0600 Message-ID: From: Scott Shattuck To: pgsql-sql@postgresql.org Subject: Re: trigger question Date: Thu, 16 May 2002 12:33:12 -0600 MIME-Version: 1.0 X-Mailer: Internet Mail Service (5.5.2653.19) Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01C1FD08.2276A0D0" X-Archive-Number: 200205/171 X-Sequence-Number: 7806 This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible. ------_=_NextPart_001_01C1FD08.2276A0D0 Content-Type: text/plain; charset="iso-8859-1" > -----Original Message----- > From: Larry Rosenman [mailto:ler@lerctr.org] > Sent: Thursday, May 16, 2002 11:51 AM > To: josh@agliodbs.com > Cc: pgsql-sql@postgresql.org > Subject: Re: [SQL] trigger question > > > On Thu, 2002-05-16 at 12:49, Josh Berkus wrote: > > Larry, > > > > > Is there a way in PL/pgSQL to have a trigger look at the > fields in a > > > %ROWTYPE variable to look for changes? > > > > > > I'm looking to be able to log the fields that are > different between old > > > and new in a trigger, but don't want to have to list > each field (It > > > changes, occasionally, but it changes). I'd also like to add a > > > timestamp and flag field when I store the record in the > log table. > > > > > > Am I just SOL, or is there a way to do this? (7.2.1, if > it matters). > > > > I'm pretty sure there is a way to do what you want, and > it's probably simpler > > than you think. > > > > However, I need you to be more explicit, with examples. I > can't quite figure > > out what is is you're trying to do. > > > > given the following table: > CREATE TABLE "networks" ( > "netblock" cidr, > "router" integer, > "interface" character varying(256), > "dest_ip" inet, > "mis_token" character(16), > "assigned_date" date, > "assigned_by" character varying(256), > "justification_now" integer, > "justification_1yr" integer, > "cust_asn" integer, > "comments" character varying(2048), > "other_reference" character varying(256), > "parent_asn" integer, > "status" integer NOT NULL, > "purpose" integer, > "last_update_by" character varying(256), > "last_update_at" timestamp with time zone, > "customer_reference" integer, > "natblock" cidr > ); > > I want to log the changes to it in a trigger. Occasionally > we add fields to it, so > I'd like the trigger to know about them automatically. I > need to log old/new for just the fields > that change (and the netblock, which is the primary key). > > I realize I can build up the insert, but that seems to be the > hard way. > It might not be what you're looking for but another possibility may be to use a function to add columns to the table and have the function update the trigger at that time. That would at least address the table/trigger consistency issue. ss Scott Shattuck Technical Pursuit Inc. ------_=_NextPart_001_01C1FD08.2276A0D0 Content-Type: text/html; charset="iso-8859-1" RE: [SQL] trigger question

> -----Original Message-----
> From: Larry Rosenman [mailto:ler@lerctr.org]
> Sent: Thursday, May 16, 2002 11:51 AM
> To: josh@agliodbs.com
> Cc: pgsql-sql@postgresql.org
> Subject: Re: [SQL] trigger question
>
>
> On Thu, 2002-05-16 at 12:49, Josh Berkus wrote:
> > Larry,
> >
> > > Is there a way in PL/pgSQL to have a trigger look at the
> fields in a
> > > %ROWTYPE variable to look for changes?
> > >
> > > I'm looking to be able to log the fields that are
> different between old
> > > and new in a trigger,  but don't want to have to list
> each field (It
> > > changes, occasionally, but it changes).  I'd also like to add a
> > > timestamp and flag field when I store the record in the
> log table.
> > >
> > > Am I just SOL, or is there a way to do this? (7.2.1, if
> it matters).
> >
> > I'm pretty sure there is a way to do what you want, and
> it's probably simpler
> > than you think. 
> >
> > However, I need you to be more explicit, with examples.  I
> can't quite figure
> > out what is is you're trying to do.
> >
>
> given the following table:
> CREATE TABLE "networks" (
>       "netblock" cidr,
>       "router" integer,
>       "interface" character varying(256),
>       "dest_ip" inet,
>       "mis_token" character(16),
>       "assigned_date" date,
>       "assigned_by" character varying(256),
>       "justification_now" integer,
>       "justification_1yr" integer,
>       "cust_asn" integer,
>       "comments" character varying(2048),
>       "other_reference" character varying(256),
>       "parent_asn" integer,
>       "status" integer NOT NULL,
>       "purpose" integer,
>       "last_update_by" character varying(256),
>       "last_update_at" timestamp with time zone,
>       "customer_reference" integer,
>       "natblock" cidr
> );
>
> I want to log the changes to it in a trigger.  Occasionally
> we add fields to it, so
> I'd like the trigger to know about them automatically.  I
> need to log old/new for just the fields
> that change (and the netblock, which is the primary key).
>
> I realize I can build up the insert, but that seems to be the
> hard way.
>

It might not be what you're looking for but another possibility may be to use a function to add columns to the table and have the function update the trigger at that time. That would at least address the table/trigger consistency issue.

ss


Scott Shattuck
Technical Pursuit Inc.

------_=_NextPart_001_01C1FD08.2276A0D0--