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@xxxxxxxxxx> 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.
