Re: 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(at)fetter(dot)org> http://fetter.org/
phone: +1 415 235 3778 AIM: dfetter666
Skype: davidfetter

Remember to vote!