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.Ganeshmail to : [EMAIL PROTECTED] Get your FREE email @ http://Volleyball.Com - Get everything for volleyball @ http://Shop.Volleyball.Com - Post a message @ http://Forum.Volleyball.Com
