Re: Generic timestamp function for updates where field

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@comcast.net
> >>
> >> 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@comcast.net
>
> 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@comcast.net