pg.ddx.io pgsql-sql@postgresql.org mailing list archivehelp / color / mirror / Atom feed
plpgsql dynamic record access 2+ messages / 2 participants [nested] [flat]
* plpgsql dynamic record access @ 2005-06-01 06:54 Ganesh <ganesh@volleyball.com> 0 siblings, 0 replies; 2+ messages in thread From: Ganesh @ 2005-06-01 06:54 UTC (permalink / raw) To: pgsql-sql <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; ^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: plpgsql dynamic record access @ 2005-06-08 17:20 Rajesh Kumar Mallah <mallah.rajesh@gmail.com> 0 siblings, 0 replies; 2+ messages in thread From: Rajesh Kumar Mallah @ 2005-06-08 17:20 UTC (permalink / raw) To: ganesh@volleyball.com; +Cc: pgsql-sql Ganesh, Did you have a look at example Example 35-2. A PL/pgSQL http://www.postgresql.org/docs/current/static/plpgsql-trigger.html Regds maLLAH ^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2005-06-08 17:20 UTC | newest] Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2005-06-01 06:54 plpgsql dynamic record access Ganesh <ganesh@volleyball.com> 2005-06-08 17:20 Re: plpgsql dynamic record access Rajesh Kumar Mallah <mallah.rajesh@gmail.com>
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