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

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.