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
