Variable column names in PL/pgSQL RECORD referencces - Mailing list pgsql-sql

<div class="Section1"><p class="MsoNormal" style="text-autospace:none"><font face="Courier New" size="2"><span
style="font-size:10.0pt;font-family:"CourierNew"">(Sorry for the redundancy – I sent this query earlier but forgot to
puta title on it.  Seems like it would be more useful with a title, so here it is again.  If there’s a moderator who
candelete my earlier message, please do so.)</span></font><p class="MsoNormal" style="text-autospace:none"><font
face="CourierNew" size="2"><span style="font-size:10.0pt;font-family:"Courier New""> </span></font><p class="MsoNormal"
style="text-autospace:none"><fontface="Courier New" size="2"><span style="font-size:10.0pt;font-family:"Courier
New"">InPL/pgSQL, is there a way to put a *variable* column-name in a dot notation reference to a RECORD
column?</span></font><pclass="MsoNormal" style="text-autospace:none"><font face="Courier New" size="2"><span
style="font-size:10.0pt;font-family:"CourierNew""> </span></font><p class="MsoNormal" style="text-autospace:none"><font
face="CourierNew" size="2"><span style="font-size:10.0pt;font-family:"Courier New"">For example, suppose I want to
writea function like the following, which is to be called by a "BEFORE INSERT" trigger:</span></font><p
class="MsoNormal"style="text-autospace:none"><font face="Courier New" size="2"><span
style="font-size:10.0pt;font-family:"CourierNew""> </span></font><p class="MsoNormal" style="text-autospace:none"><font
face="CourierNew" size="2"><span style="font-size:10.0pt;font-family:"Courier New"">CREATE OR REPLACE FUNCTION foo (  )
RETURNSTRIGGER AS </span></font><p class="MsoNormal" style="text-autospace:none"><font face="Courier New"
size="2"><spanstyle="font-size:10.0pt;font-family:"Courier New"">    '</span></font><p class="MsoNormal"
style="text-autospace:none"><fontface="Courier New" size="2"><span style="font-size:10.0pt;font-family:"Courier
New"">   DECLARE </span></font><p class="MsoNormal" style="text-autospace:none"><font face="Courier New" size="2"><span
style="font-size:10.0pt;font-family:"CourierNew"">        var VARCHAR;</span></font><p class="MsoNormal"
style="text-autospace:none"><fontface="Courier New" size="2"><span style="font-size:10.0pt;font-family:"Courier
New"">   BEGIN</span></font><p class="MsoNormal" style="text-autospace:none"><font face="Courier New" size="2"><span
style="font-size:10.0pt;font-family:"CourierNew"">        var := TG_ARGV[0]       </span></font><p class="MsoNormal"
style="text-autospace:none"><fontface="Courier New" size="2"><span style="font-size:10.0pt;font-family:"Courier
New"">       NEW.<the column whose name is the value of var> := ''whatever'';</span></font><p class="MsoNormal"
style="text-autospace:none"><fontface="Courier New" size="2"><span style="font-size:10.0pt;font-family:"Courier
New"">       RETURN NEW;     </span></font><p class="MsoNormal" style="text-autospace:none"><font face="Courier New"
size="2"><spanstyle="font-size:10.0pt;font-family:"Courier New"">    END;</span></font><p class="MsoNormal"
style="text-autospace:none"><fontface="Courier New" size="2"><span style="font-size:10.0pt;font-family:"Courier
New"">   '</span></font><p class="MsoNormal" style="text-autospace:none"><font face="Courier New" size="2"><span
style="font-size:10.0pt;font-family:"CourierNew"">    LANGUAGE 'plpgsql'</span></font><p class="MsoNormal"
style="text-autospace:none"><fontface="Courier New" size="2"><span style="font-size:10.0pt;font-family:"Courier
New"">;</span></font><pclass="MsoNormal" style="text-autospace:none"><font face="Courier New" size="2"><span
style="font-size:10.0pt;font-family:"CourierNew""> </span></font><p class="MsoNormal" style="text-autospace:none"><font
face="CourierNew" size="2"><span style="font-size:10.0pt;font-family:"Courier New"">The aim of this uninteresting
functionis to assign the value 'whatever' to the table column that is passed in by the calling trigger as TG_ARGV[0],
i.e.the first calling argument.  </span></font><p class="MsoNormal" style="text-autospace:none"><font face="Courier
New"size="2"><span style="font-size:10.0pt;font-family:"Courier New""> </span></font><p class="MsoNormal"
style="text-autospace:none"><fontface="Courier New" size="2"><span style="font-size:10.0pt;font-family:"Courier
New"">WhatI don't know is what to put into the dot notation in place of ".<the column whose name is the value of
var>"so that the column of NEW that is addressed by the assignment statement is the one passed in as the first
argument. Is there any PL/pgSQL construct that could be substituted in here to achieve this result?</span></font><p
class="MsoNormal"style="text-autospace:none"><font face="Courier New" size="2"><span
style="font-size:10.0pt;font-family:"CourierNew""> </span></font><p class="MsoNormal" style="text-autospace:none"><font
face="CourierNew" size="2"><span style="font-size:10.0pt;font-family:"Courier New"">If not, can anybody suggest a way
towrite a trigger-called function that would accomplish the same result?</span></font><p class="MsoNormal"
style="text-autospace:none"><fontface="Courier New" size="2"><span style="font-size:10.0pt;font-family:"Courier
New""> </span></font><pclass="MsoNormal" style="text-autospace:none"><font face="Courier New" size="2"><span
style="font-size:10.0pt;font-family:"CourierNew"">In case it's not obvious, the underlying goal is to write a single
trigger-calledfunction that could modify different columns for each trigger that called it, where each trigger
specifiedthe target column by a calling argument (or by any other viable mechanism).</span></font><p class="MsoNormal"
style="text-autospace:none"><fontface="Courier New" size="2"><span style="font-size:10.0pt;font-family:"Courier
New""> </span></font><pclass="MsoNormal" style="text-autospace:none"><font face="Courier New" size="2"><span
style="font-size:10.0pt;font-family:"CourierNew"">~ TIA</span></font><p class="MsoNormal"
style="text-autospace:none"><fontface="Courier New" size="2"><span style="font-size:10.0pt;font-family:"Courier New"">~
Ken</span></font><pclass="MsoNormal" style="text-autospace:none"><font face="Courier New" size="2"><span
style="font-size:10.0pt;font-family:"CourierNew""> </span></font><p class="MsoNormal"><font face="Verdana"
size="2"><spanstyle="font-size:10.0pt; 
font-family:Verdana"> </span></font></div>