Re: Generic timestamp function for updates where field

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(at)comcast(dot)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(at)comcast(dot)net