Quick Links

plpgsql dynamic record access

From:	Ganesh <ganesh(at)volleyball(dot)com>
To:	pgsql-sql(at)postgresql(dot)org
Subject:	plpgsql dynamic record access
Date:	2005-06-01 06:54:25
Message-ID:	20050601065425.46B8937DF0@sitemail.everyone.net
Views:	Raw Message \| Whole Thread \| Download mbox \| Resend email
Thread:
Lists:	pgsql-sql

<html><body> 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(at)volleyball(dot)com   <hr>Get your FREE email @ http://Volleyball.Com - Get everything for volleyball @ http://Shop.Volleyball.Com - Post a message @ http://Forum.Volleyball.Com </body></html>

Attachment	Content-Type	Size
unknown_filename	text/html	4.7 KB

Browse pgsql-sql by date

	From	Date	Subject
Next Message	lucas	2005-06-01 11:49:00	Re: Sum() rows
Previous Message	Ramakrishnan Muralidharan	2005-06-01 04:59:29	Re: Multiple SRF parameters from query