Thread: Dynamic access to record columns (in PL/pgsql)
Hello I have a trigger procedure which I want to be called after insert or update on different tables. In this procedure, I need to find the values of key fields for the affected table in the NEW record, but I can't find a way to access a field the name of which I only know at execution time. I tried many workarounds, for example this: fieldname := name of key field; EXECUTE 'SELECT NEW.'||fieldname INTO fieldvalue; But none of them worked. I found a one-year-old thread about this kind of dynamic access, which proposed a syntax like this: NEW.(fieldname), but this hasn't be implemented in the current version and, apparently, no different methods where suggested. Can anybody suggest me how to do that? Right now I can only think of programmatically generating one different procedure for every table I want to track (there are 42), but this would be very clunky solution. Thanks for your time Daniele Mazzini
Daniele Mazzini <daniele.mazzini@libero.it> writes: > I have a trigger procedure which I want to be called after insert or > update on different tables. In this procedure, I need to find the values > of key fields for the affected table in the NEW record, but I can't find > a way to access a field the name of which I only know at execution time. There is no way to do that in plpgsql. You could do it in the other PLs (eg plperl, pltcl) since they are not as strongly typed as plpgsql. regards, tom lane
Tom Lane wrote: > Daniele Mazzini <daniele.mazzini@libero.it> writes: > >> I have a trigger procedure which I want to be called after insert or >> update on different tables. In this procedure, I need to find the values >> of key fields for the affected table in the NEW record, but I can't find >> a way to access a field the name of which I only know at execution time. >> > > There is no way to do that in plpgsql. You could do it in the other PLs > (eg plperl, pltcl) since they are not as strongly typed as plpgsql. > > regards, tom lane > > Well, I wouldn't go that far. A little more information might point to a solution here. Given any one table that this trigger might fire on, are the fields that you need to check defined? I.e., if the trigger fires on table A, will you always be looking at column A.b and on table B column B.a? If so, when you create the trigger, pass in some kind of variable that the trigger can use to know what field it's looking for. If that doesn't make enough sense, let me know and I can give you a more fleshed out example of what I'm talking about. -- erik jones <erik@myemma.com> software development emma(r)