Thanks Adrian, I'll see what I can do with that, I'll learn a lot by going through what you've done. Adrian Klaver wrote: > > On Sunday 31 December 2006 8:48 am, novnov wrote: >> OK. python would be the preference, if anyone is interested in showing me >> how it would be done, I've never used one of the dynamic languages with >> postgres. >> >> Why would not be possible in plpgsql? It has loop etc, the only part I'm >> not sure it can do it use the variable as field name. >> > >> > http://archives.postgresql.org/ > Here is a function I wrote in python to do something similar. My > timestamp > fields are of the form tc_ts_update where tc is a table code that can be > found by looking up the table name in the table_code table. In pl/pythonu > that ships with 8.2 it is no longer necessary to do the relid look up. > There > is a TD["table_name"] variable that returns the table name directly. > > CREATE OR REPLACE FUNCTION public.ts_update() > RETURNS trigger AS > $Body$ > table_oid=TD["relid"] > plan_name=plpy.prepare("SELECT relname FROM pg_class WHERE > oid=$1",["oid"]) > plan_code=plpy.prepare("SELECT tc_table_code FROM table_code WHERE > tc_table_name=$1",["text"]) > rs_name=plpy.execute(plan_name,[table_oid]) > rs_code=plpy.execute(plan_code,[rs_name[0]["relname"]]) > fld_name="_ts_update" > tbl_code=rs_code[0]["tc_table_code"] > full_name=tbl_code+fld_name > TD["new"][full_name]="now()" > return "MODIFY" > $Body$ > LANGUAGE plpythonu SECURITY DEFINER; > -- > Adrian Klaver > aklaver@xxxxxxxxxxx > > ---------------------------(end of broadcast)--------------------------- > TIP 2: Don't 'kill -9' the postmaster > > -- View this message in context: http://www.nabble.com/Generic-timestamp-function-for-updates-where-field-names-vary-tf2899327.html#a8108979 Sent from the PostgreSQL - general mailing list archive at Nabble.com.
