Adrian Klaver wrote: > > On Wednesday 03 January 2007 12:13 am, novnov wrote: >> 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 >> >> Here is what I have tried, it fails on the >> TD["NEW"][varFieldName]="now()" >> line. >> Do I need the Return? >> I'm passing in the table prefix as a param. >> I set to VOLATILE not SECURITY DEFINER (wasn't sure what that was) >> >> CREATE OR REPLACE FUNCTION "public"."datem_update"() >> RETURNS trigger AS >> $BODY$ >> varPrefix=TG_ARGV[0] >> varFieldName=varPrefix+"_datem" >> TD["NEW"][varFieldName]="now()" >> RETURN "Modify" > Try return "Modify". I believe the problem is actually the upper case > RETURN. >> $BODY$ >> LANGUAGE 'plpythonu' VOLATILE; > -- > Adrian Klaver > aklaver@xxxxxxxxxxx > Thanks Adrian, 'return' works better. But there may be a namespace issue with TG_ARGV. The error I get is "exceptions.NameError: global name TG_ARGV is not defined." I have been unable to find anything on this by googling the web or usenet. Do the postgres names like TG_ARGV need special treatment inside a python function? tg_argv[0] (ie lowercase) did no better. As an experiment I replaced tg_argv with a hard coded the prefix value, and found that it didn't like NEW either, 'new' is better. But with that change the function works, so the TG_ARGV issue is the last one. -- View this message in context: http://www.nabble.com/Generic-timestamp-function-for-updates-where-field-names-vary-tf2899327.html#a8144550 Sent from the PostgreSQL - general mailing list archive at Nabble.com.
