Re: Generic timestamp function for updates where field - Mailing list pgsql-general

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.
--
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.