X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 5FEC952863 for ; Thu, 9 Jun 2005 15:16:55 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 85184-01 for ; Thu, 9 Jun 2005 18:16:50 +0000 (GMT) Received: from mail.astudios.com (ns1.astudios.com [66.49.77.3]) by svr1.postgresql.org (Postfix) with ESMTP id 2775352814 for ; Thu, 9 Jun 2005 15:16:48 -0300 (ADT) thread-index: AcVtH4xYdMEx+W7jRbOHZuCIlCCedw== Received: from webserver ([10.0.0.10]) by mail.astudios.com with Microsoft SMTPSVC(6.0.3790.1830); Thu, 9 Jun 2005 14:17:51 -0400 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830 Content-Class: urn:content-classes:message Importance: normal From: "Chris Hoover" To: Date: Thu, 09 Jun 2005 18:17:24 (GMT) X-MSMail-Priority: Normal X-Mailer: AspMail 4.0 4.03 (SMT492C4AF) Subject: Help with function MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Message-ID: X-OriginalArrivalTime: 09 Jun 2005 18:17:51.0343 (UTC) FILETIME=[8C3207F0:01C56D1F] X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.235 tagged_above=0 required=5 tests=AWL, INVALID_DATE X-Spam-Level: X-Archive-Number: 200506/61 X-Sequence-Number: 17816 I am trying to write a very generic function to be used for table = auditing. I am kind of stuck on how to make the function use the column = names I pull from the db. Is there anyway to do what I have below? My = main concern is this segment: if (new.columnName !=3D old.columnName or (old.columnName is null and new.columnName is not null) or (old.columnName is not null and new.columnName is null) ) then How can I get PostgreSQL to substitute the selected column name for the = old.columnName/new.columnName? Thanks, Chris CREATE OR REPLACE FUNCTION table_update_trig_func() RETURNS "trigger" AS ' declare /* setup the local variables */ loopRec record; columnName varchar; sqlStr varchar(2000); begin /* write out to the log file where we are */ raise notice \'in start of table_update_trig_func\'; =20 /* setup the current sql string */ sqlStr :=3D \'select attname as columnName \'; sqlStr :=3D sqlStr || \'from pg_class, pg_attribute, pg_type \'; sqlStr :=3D sqlStr || \'where pg_class.relname =3D \'\'table\'\' \'; sqlStr :=3D sqlStr || \'and pg_attribute.attrelid =3D pg_class.oid \'; sqlStr :=3D sqlStr || \'and pg_attribute.atttypid =3D pg_type.oid \'; sqlStr :=3D sqlStr || \'and pg_type.typname not in = (\'\'oid\'\',\'\'tid\'\',\'\'xid\'\',\'\'cid\'\',\'\'oidvector\'\') \'; sqlstr :=3D sqlStr || \'order by attname \'; =20 /* start the check of each column in the table */ for loopRec in execute sqlStr loop raise notice \'value of loopRec.columnName is %\', = loopRec.columnName; columnName :=3D loopRec.columnName; =20 if (new.columnName !=3D old.columnName or (old.columnName is null and new.columnName is not null) or (old.columnName is not null and new.columnName is null) ) then raise notice \'table_update_trig_func - calling insert_into_log = function\'; =20 select * from eedi.insert_into_clmchglog(); end if; end loop; return new; end; ' LANGUAGE 'plpgsql' STABLE SECURITY DEFINER;