On Wednesday 03 January 2007 9:10 am, novnov wrote: > 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. Replace TG_ARGV[0] with TD["args"][0] For complete documentation see http://www.postgresql.org/docs/8.2/interactive/plpython.html -- Adrian Klaver aklaver@xxxxxxxxxxx
