pg.ddx.io pgsql-sql@postgresql.org mailing list archive help / color / mirror / Atom feed From: Ganesh <ganesh@volleyball.com>
To: pgsql-sql@postgresql.org
Subject: plpgsql dynamic record access
Date: Tue, 31 May 2005 23:54:25 -0700 (PDT)
Message-ID: <20050601065425.46B8937DF0@sitemail.everyone.net> (raw )
<html><body> <font style="font-family: arial,helvetica,sans-serif;" size="2">Hello,
<br>
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.
<br>
<br>
<br>
--/* function for giving the field attributes of a given table */
<br>
CREATE FUNCTION get_fld_name(text) RETURNS _varchar AS '
<br>
DECLARE
<br>
out_fld text[20] := array[''name'',''no''];
<br>
<br>
BEGIN
<br>
RETURN out_fld;
<br>
END;
<br>
' LANGUAGE 'plpgsql';
<br>
<br>
--/** function for recording the changes **/
<br>
<br>
CREATE FUNCTION my_func_test() RETURNS OPAQUE AS '
<br>
DECLARE
<br>
<br>
flds text[20];
<br>
fldname text;
<br>
<br>
ofld text;
<br>
nfld text;
<br>
<br>
BEGIN
<br>
<br>
flds := (SELECT * from get_fld_name(TG_RELNAME));
<br>
IF TG_OP=''INSERT'' THEN
<br>
ofld=nfld;
<br>
END IF;
<br>
FOR i IN 1..20 loop
<br>
IF flds[i] <> ''NULL'' THEN
<br>
fldname := flds[i];
<br>
<br>
<span style="background-color: rgb(255, 255, 255); color: rgb(0, 0, 153);">--/**************** Here is the problem ****************/
</span><br style="background-color: rgb(255, 255, 255); color: rgb(0, 0, 153);">
<span style="background-color: rgb(255, 255, 255); color: rgb(0, 0, 153);"> </span><br style="background-color: rgb(255, 255, 255); color: rgb(0, 0, 153);">
<span style="background-color: rgb(255, 255, 255); color: rgb(0, 0, 153);"> ofld := OLD.fldname; -- this should return Old's value
</span><br style="background-color: rgb(255, 255, 255); color: rgb(0, 0, 153);">
<span style="background-color: rgb(255, 255, 255); color: rgb(0, 0, 153);"> nfld := NEW.fldname; -- this should return New's value
</span><br style="background-color: rgb(255, 255, 255); color: rgb(0, 0, 153);">
<span style="background-color: rgb(255, 255, 255); color: rgb(0, 0, 153);"> </span><br style="background-color: rgb(255, 255, 255); color: rgb(0, 0, 153);">
<span style="background-color: rgb(255, 255, 255); color: rgb(0, 0, 153);">--/****I want to get he value from OLD.name or OLD.No *******/
</span><br style="background-color: rgb(255, 255, 255); color: rgb(0, 0, 153);">
<span style="background-color: rgb(255, 255, 255); color: rgb(0, 0, 153);"> </span><br style="background-color: rgb(255, 255, 255); color: rgb(0, 0, 153);">
<span style="background-color: rgb(255, 255, 255); color: rgb(0, 0, 153);">--/***********************************************************
</span><br style="background-color: rgb(255, 255, 255); color: rgb(0, 0, 153);">
<span style="background-color: rgb(255, 255, 255); color: rgb(0, 0, 153);"> </span><br style="background-color: rgb(255, 255, 255); color: rgb(0, 0, 153);">
<span style="background-color: rgb(255, 255, 255); color: rgb(0, 0, 153);">
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.
</span><br style="background-color: rgb(255, 255, 255); color: rgb(0, 0, 153);">
<span style="background-color: rgb(255, 255, 255); color: rgb(0, 0, 153);"> </span><br style="background-color: rgb(255, 255, 255); color: rgb(0, 0, 153);">
<span style="background-color: rgb(255, 255, 255); color: rgb(0, 0, 153);">***********************************************************/</span>
<br>
<br>
ofld := old_data[i];
<br>
nfld := new_data[i];
<br>
<br>
INSERT INTO Audit_table values (5,TG_RELNAME,''now'',TG_OP,fldname,ofld,nfld,current_user);
<br>
<br>
RAISE NOTICE ''ss : % '',fldname;
<br>
RAISE NOTICE ''ofldname : % '',ofld;
<br>
RAISE NOTICE ''nfldname : % '',nfld;
<br>
<br>
END IF;
<br>
END LOOP;
<br>
RETURN NEW;
<br>
END;
<br>
' LANGUAGE 'plpgsql';
<br>
<br>
<br>
<br>
CREATE TRIGGER test_trig AFTER UPDATE ON test
<br>
FOR EACH ROW EXECUTE PROCEDURE my_func_test();
<br>
<br>
<br>
<br>
Please Help me..
<br>
<br>
Thanks ,<br> <br>R.Ganesh<br><br>mail to : ganesh@volleyball.com<br><br></font> <br> <br><hr>Get your FREE email @ http://Volleyball.Com - Get everything for volleyball @ http://Shop.Volleyball.Com - Post a message @ http://Forum.Volleyball.Com<br></body></html ;
view thread (2+ messages) latest in thread
Message-ID: <20050601065425.46B8937DF0@sitemail.everyone.net>
Permalink: ../20050601065425.46B8937DF0@sitemail.everyone.net/
Also on: postgresql.org/message-id/20050601065425.46B8937DF0@sitemail.everyone.net
copy link · copy postgr.es
reply Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: ganesh@volleyball.com
Subject: Re: plpgsql dynamic record access
In-Reply-To: <20050601065425.46B8937DF0@sitemail.everyone.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox