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