From ganesh@volleyball.com Wed Jun 1 06:54:27 2005 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id B337852933 for ; Wed, 1 Jun 2005 03:54:27 -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 99670-05 for ; Wed, 1 Jun 2005 06:54:25 +0000 (GMT) Received: from omta06.mta.everyone.net (sitemail.everyone.net [216.200.145.51]) by svr1.postgresql.org (Postfix) with ESMTP id 83BA55280B for ; Wed, 1 Jun 2005 03:54:24 -0300 (ADT) Received: from sitemail.everyone.net (bigiplb-dsnat [172.16.0.19]) by omta06.mta.everyone.net (Postfix) with ESMTP id 6D93B3FD7C for ; Tue, 31 May 2005 23:54:25 -0700 (PDT) Received: by sitemail.everyone.net (Postfix, from userid 99) id 46B8937DF0; Tue, 31 May 2005 23:54:25 -0700 (PDT) Content-Type: text/html Content-Disposition: inline Content-Transfer-Encoding: 7bit Mime-Version: 1.0 X-Mailer: MIME-tools 5.41 (Entity 5.404) Date: Tue, 31 May 2005 23:54:25 -0700 (PDT) From: Ganesh To: pgsql-sql@postgresql.org Subject: plpgsql dynamic record access Reply-To: ganesh@volleyball.com X-Originating-Ip: [61.11.64.189] X-Eon-Sig: AQFpfKJCnVuhAANV+gEAAAAB,7d76ebc936451ee496463c27419cbb2c Message-Id: <20050601065425.46B8937DF0@sitemail.everyone.net> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=1.393 tagged_above=0 required=5 tests=HTML_60_70, HTML_MESSAGE, MIME_HTML_ONLY, UPPERCASE_25_50 X-Spam-Level: * X-Archive-Number: 200506/1 X-Sequence-Number: 21602 Hello,
       I have searched everywere for a solution pls help me. I am building a trigger which would keep track of every modification to some table. here i am generlising the function. the following is the code in plpgsql.

--/* function for giving the field attributes of a given table */
CREATE FUNCTION get_fld_name(text) RETURNS _varchar AS '
DECLARE
   out_fld text[20] := array[''name'',''no''];

BEGIN
RETURN out_fld;
END;
' LANGUAGE 'plpgsql';

--/** function for recording the changes **/

CREATE FUNCTION my_func_test() RETURNS OPAQUE AS '
DECLARE

flds text[20];
fldname text;

ofld text;
nfld text;

BEGIN

flds := (SELECT * from get_fld_name(TG_RELNAME));
IF TG_OP=''INSERT'' THEN
      ofld=nfld;
END IF;
FOR i IN 1..20 loop
    IF flds[i] <> ''NULL'' THEN
       fldname := flds[i];

--/**************** Here is the problem ****************/

      ofld := OLD.fldname; -- this should return Old's value
      nfld := NEW.fldname; -- this should return New's value

--/****I want to get he value from OLD.name or OLD.No *******/

--/***********************************************************

    Is there any way to concatnate the 2 Objects [(OLD) and (Fldname) ] so that fldname would be converted to the value but the object OLD remains same.

***********************************************************/

         ofld := old_data[i];
         nfld := new_data[i];

    INSERT INTO Audit_table values (5,TG_RELNAME,''now'',TG_OP,fldname,ofld,nfld,current_user);

         RAISE NOTICE ''ss : % '',fldname;
   RAISE NOTICE ''ofldname : % '',ofld;
   RAISE NOTICE ''nfldname : % '',nfld;

    END IF;
END LOOP;
RETURN NEW;
END;
' LANGUAGE 'plpgsql';

CREATE TRIGGER test_trig AFTER UPDATE ON test
FOR EACH ROW EXECUTE PROCEDURE my_func_test();

Please Help me..

Thanks ,

R.Ganesh

mail to : ganesh@volleyball.com

Get your FREE email @ http://Volleyball.Com - Get everything for volleyball @ http://Shop.Volleyball.Com - Post a message @ http://Forum.Volleyball.Com
From mallah.rajesh@gmail.com Wed Jun 8 17:20:21 2005 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id D949352849 for ; Wed, 8 Jun 2005 14:20:21 -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 94313-02 for ; Wed, 8 Jun 2005 17:20:13 +0000 (GMT) Received: from rproxy.gmail.com (rproxy.gmail.com [64.233.170.206]) by svr1.postgresql.org (Postfix) with ESMTP id D63AF52816 for ; Wed, 8 Jun 2005 14:20:11 -0300 (ADT) Received: by rproxy.gmail.com with SMTP id i8so334346rne for ; Wed, 08 Jun 2005 10:20:12 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:reply-to:to:subject:cc:mime-version:content-type:content-transfer-encoding:content-disposition; b=sYvLtLWNqIDw2MyCwDP1pWTh8DUEWylo/0XwTx0tjDZ+HDPgQbEYFySK2zuWOMoi2JMZvWItNqgfM601wWaQhyBSXR3C4ienmjliPkRjgTqAJTEcDMmlsRo/xZXAv/T5HS3d3uQbrhZfK5hudk4yS4NKcHFDUMP7i7bY97WhF0k= Received: by 10.38.97.66 with SMTP id u66mr3773129rnb; Wed, 08 Jun 2005 10:20:12 -0700 (PDT) Received: by 10.38.208.78 with HTTP; Wed, 8 Jun 2005 10:20:12 -0700 (PDT) Message-ID: Date: Wed, 8 Jun 2005 22:50:12 +0530 From: Rajesh Kumar Mallah Reply-To: Rajesh Kumar Mallah To: ganesh@volleyball.com Subject: Re: plpgsql dynamic record access Cc: pgsql-sql@postgresql.org Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.053 tagged_above=0 required=5 tests=AWL, RCVD_BY_IP X-Spam-Level: X-Archive-Number: 200506/115 X-Sequence-Number: 21716 Ganesh, Did you have a look at example Example 35-2. A PL/pgSQL http://www.postgresql.org/docs/current/static/plpgsql-trigger.html Regds maLLAH