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