Skip site navigation (1) Skip section navigation (2)

Re: Generic timestamp function for updates where field

From: Adrian Klaver <aklaver(at)comcast(dot)net>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: Generic timestamp function for updates where field
Date: 2006-12-31 17:58:40
Message-ID: 200612310958.40802.aklaver@comcast.net (view raw or flat)
Thread:
Lists: pgsql-general
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

In response to

Responses

pgsql-general by date

Next:From: novnovDate: 2006-12-31 18:31:14
Subject: Re: Generic timestamp function for updates where field
Previous:From: Chad WagnerDate: 2006-12-31 17:08:12
Subject: Re: slow speeds after 2 million rows inserted

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group