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

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.