Generic timestamp function for updates where field names vary

On Sat, Dec 30, 2006 at 10:33:01AM -0800, novnov wrote:

The pagila database has generic trigger function called last_updated() (shown
below) which is used to update timestamp columns in various tables. The
reason I can't use the function 'as is' for my own purposes is that in my
app the timestamp fields are not all named alike. The field names do follow
a pattern, two example names would be "user_datem "and "item_datem".

In cases like these, it's better to use a more dynamic language for
your trigger like PL/Perl.

Cheers,
David.

I know
I could change my db so that all these timestamp fields are named "datem",
but I'd prefer to keep the names distinct, and of course I don't want to
create a tigger funtion for each table. Using the pagila trigger function as
a starting point, can someone suggest a solution? I am pretty sure that a
simple solution would be to pass in the prefix value, and concatenate with
the common "_datem". Or is there a better solution? I will give the
approach I've outlined a try, but I'm not even sure it's doable (primarliy,
using the contatenated field name inplace of the "last-update" in
"NEW.last_update = CURRENT_TIMESTAMP;", that's just stuff I've not done in
plpgsql)...I'm all thumbs with plpgsql syntax, so anyone that wants to lay a
solution down would be helping out a lot.

From pagila:

CREATE or REPLACE FUNCTION "public"."last_updated"()
RETURNS "pg_catalog"."trigger" AS
$BODY$
BEGIN
NEW.last_update = CURRENT_TIMESTAMP;
RETURN NEW;
END
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
--
View this message in context: http://www.nabble.com/Generic-timestamp-function-for-updates-where-field-names-vary-tf2899327.html#a8100353
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org/

--
David Fetter <david@fetter.org> http://fetter.org/
phone: +1 415 235 3778 AIM: dfetter666
Skype: davidfetter

Remember to vote!

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.

David Fetter wrote:

On Sat, Dec 30, 2006 at 10:33:01AM -0800, novnov wrote:

The pagila database has generic trigger function called last_updated()
(shown
below) which is used to update timestamp columns in various tables. The
reason I can't use the function 'as is' for my own purposes is that in my
app the timestamp fields are not all named alike. The field names do
follow
a pattern, two example names would be "user_datem "and "item_datem".

In cases like these, it's better to use a more dynamic language for
your trigger like PL/Perl.

Cheers,
David.

I know
I could change my db so that all these timestamp fields are named
"datem",
but I'd prefer to keep the names distinct, and of course I don't want to
create a tigger funtion for each table. Using the pagila trigger function
as
a starting point, can someone suggest a solution? I am pretty sure that a
simple solution would be to pass in the prefix value, and concatenate
with
the common "_datem". Or is there a better solution? I will give the
approach I've outlined a try, but I'm not even sure it's doable
(primarliy,
using the contatenated field name inplace of the "last-update" in
"NEW.last_update = CURRENT_TIMESTAMP;", that's just stuff I've not done
in
plpgsql)...I'm all thumbs with plpgsql syntax, so anyone that wants to
lay a
solution down would be helping out a lot.

From pagila:

CREATE or REPLACE FUNCTION "public"."last_updated"()
RETURNS "pg_catalog"."trigger" AS
$BODY$
BEGIN
NEW.last_update = CURRENT_TIMESTAMP;
RETURN NEW;
END
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
--
View this message in context:
http://www.nabble.com/Generic-timestamp-function-for-updates-where-field-names-vary-tf2899327.html#a8100353
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org/

--
David Fetter <david@fetter.org> http://fetter.org/
phone: +1 415 235 3778 AIM: dfetter666
Skype: davidfetter

Remember to vote!

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org/

--
View this message in context: http://www.nabble.com/Generic-timestamp-function-for-updates-where-field-names-vary-tf2899327.html#a8108262
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

When responding just now I mentioned loops because another issue I'm working
on involves those, loops wouldn't be involved here.

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.

David Fetter wrote:

On Sat, Dec 30, 2006 at 10:33:01AM -0800, novnov wrote:

The pagila database has generic trigger function called last_updated()
(shown
below) which is used to update timestamp columns in various tables. The
reason I can't use the function 'as is' for my own purposes is that in
my
app the timestamp fields are not all named alike. The field names do
follow
a pattern, two example names would be "user_datem "and "item_datem".

In cases like these, it's better to use a more dynamic language for
your trigger like PL/Perl.

Cheers,
David.

I know
I could change my db so that all these timestamp fields are named
"datem",
but I'd prefer to keep the names distinct, and of course I don't want to
create a tigger funtion for each table. Using the pagila trigger
function as
a starting point, can someone suggest a solution? I am pretty sure that
a
simple solution would be to pass in the prefix value, and concatenate
with
the common "_datem". Or is there a better solution? I will give the
approach I've outlined a try, but I'm not even sure it's doable
(primarliy,
using the contatenated field name inplace of the "last-update" in
"NEW.last_update = CURRENT_TIMESTAMP;", that's just stuff I've not done
in
plpgsql)...I'm all thumbs with plpgsql syntax, so anyone that wants to
lay a
solution down would be helping out a lot.

From pagila:

CREATE or REPLACE FUNCTION "public"."last_updated"()
RETURNS "pg_catalog"."trigger" AS
$BODY$
BEGIN
NEW.last_update = CURRENT_TIMESTAMP;
RETURN NEW;
END
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
--
View this message in context:
http://www.nabble.com/Generic-timestamp-function-for-updates-where-field-names-vary-tf2899327.html#a8100353
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org/

--
David Fetter <david@fetter.org> http://fetter.org/
phone: +1 415 235 3778 AIM: dfetter666
Skype: davidfetter

Remember to vote!

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org/

--
View this message in context: http://www.nabble.com/Generic-timestamp-function-for-updates-where-field-names-vary-tf2899327.html#a8108294
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

On Sun, Dec 31, 2006 at 08:48:07AM -0800, novnov wrote:

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.

The "dynamic" in dynamic language usually refers to dynamic typing.
pl/pgsql is a strictly typed language which means one can look at the
code and determine what type each variable will be.

Languages like perl and python are dynamic because what type a variable
is does not matter until runtime and the type can change every time you
run it.

The problem with using a variable as a field name is that you can no
longer tell what type the result will be. It could be any type
supported by postgres. pl/pgsql can't handle that, perl and python
can...

Hope this helps,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/

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

Yes thanks that does make it clear.

Martijn van Oosterhout wrote:

On Sun, Dec 31, 2006 at 08:48:07AM -0800, novnov wrote:

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.

The "dynamic" in dynamic language usually refers to dynamic typing.
pl/pgsql is a strictly typed language which means one can look at the
code and determine what type each variable will be.

Languages like perl and python are dynamic because what type a variable
is does not matter until runtime and the type can change every time you
run it.

The problem with using a variable as a field name is that you can no
longer tell what type the result will be. It could be any type
supported by postgres. pl/pgsql can't handle that, perl and python
can...

Hope this helps,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/

From each according to his ability. To each according to his ability to
litigate.

--
View this message in context: http://www.nabble.com/Generic-timestamp-function-for-updates-where-field-names-vary-tf2899327.html#a8108978
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

Thanks Adrian, I'll see what I can do with that, I'll learn a lot by going
through what you've done.

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

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

--
View this message in context: http://www.nabble.com/Generic-timestamp-function-for-updates-where-field-names-vary-tf2899327.html#a8108979
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

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

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

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.

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

Thanks, that did it, and I'd not seen that set of docs yet, should be
helpful.

--
View this message in context: http://www.nabble.com/Generic-timestamp-function-for-updates-where-field-names-vary-tf2899327.html#a8155138
Sent from the PostgreSQL - general mailing list archive at Nabble.com.