Re: Generic timestamp function for updates where field

[novnov <novnovice@xxxxxxxxx>]

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"
$BODY$
LANGUAGE 'plpythonu' VOLATILE;
-- 
View this message in context: http://www.nabble.com/Generic-timestamp-function-for-updates-where-field-names-vary-tf2899327.html#a8137098
Sent from the PostgreSQL - general mailing list archive at Nabble.com.