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
