Re: "record" datatype - plpgsql - Mailing list pgsql-sql

<br /> I attempted the same thing in pl/pgsql but was unable to find a satisfactory solution using it.  I eventually
startedusing tcl as the procedural language to get this type of effect. Tcl casts NEW and OLD into arrays in a manner
thatmakes it possible.<br /><br /> Original post:<br /> Subject: PL/Pgsql Trigger fcuntion issue..<br /> This is
supposeto pull all the columns of the table that initiated the trigger func from the sys catalogs, loop through them
andput everything that has changed between OLD and NEW into a comma delimited string for input into a log like table
forfuture analysis via middleware (php,perl..,etc).  Here is the problem, OLD.A results in 'old does not have field A',
whichis true. I cant get the OLD and NEW record objects to realize that I want OLD.<string value of A> for the
columnname instead of an explicit A as the column name.  The only way I can find to make this work is by using TCL for
theprocedural language because of the way it casts the OLD and NEW into an associative array instead of a RECORD
object,but by using TCL I will lose functionallity in the "complete" version of the following function which has been
strippedto show my specific problem so using TCL is currently not in my list of options.  Any insight will be greatly
appreciated.<br /> create or replace function hmm() returns TRIGGER as ' <br /> DECLARE <br /> table_cols RECORD; <br
/>attribs VARCHAR; <br /> A VARCHAR; <br /> BEGIN <br /> IF TG_OP = ''UPDATE'' THEN <br />     FOR table_cols IN select
attnamefrom pg_attribute where attrelid = TG_RELID and attnum > -1 LOOP <br />      A := table_cols.attname; <br />
    IF OLD.A != NEW.A THEN  --Begin problem <br />       IF attribs != '''' THEN <br />       attribs := attribs ||
'',''|| table_cols.attname || ''='' || OLD.A || ''->'' || NEW.A; <br />       ELSE <br />       attribs :=
table_cols.attname|| ''='' || OLD.A || ''->'' || NEW.A; <br />       END IF; <br />      END IF; <br />     END
LOOP;<br /> END IF; <br /> RAISE EXCEPTION ''%'', attribs; <br /> RETURN NULL; <br /> END; <br /> ' Language 'plpgsql';
<br/><br /><br /> 1 of my TCL functions (note: a person with good TCL experience may be able to make this shorter or
faster,first thing I ever really did in TCL but it works fast and reliable on a large database):<br /><br /> create
functiontouch_loggerINSUPD() returns OPAQUE as '<br /> spi_exec "select current_user as tguser"<br /> spi_exec "select
relnameas tgname from pg_class where relfilenode = $TG_relid"<br /> if {[string equal -nocase $tguser audit] } {return
OK}<br /> if {[string equal -nocase $TG_op INSERT] } {<br />   set forins ""<br />  foreach i $TG_relatts {<br />   set
forins"$forins^[array get NEW $i]"<br />  }<br />  set themid [lindex [split [array get NEW acid] " "] 1]<br />
 spi_exec-array C "INSERT INTO TOUCHLOG (username,tablename,recid,modtime,action,attributes) values
(''$tguser'',''$tgname'',''$themid'',''now'',''$TG_op'',''$forins'')"<br/> }<br /> if {[string equal -nocase $TG_op
UPDATE]} {<br />   set forins ""<br />   set toadd ""<br />  foreach i $TG_relatts {<br />    if {[string equal -nocase
[arrayget NEW $i] [array get OLD $i]] == 0} {<br />     set toadd "[array get OLD $i]-[array get NEW $i]"<br />     set
forins"$forins^[string trim $toadd \']"<br />    }<br />  }<br />  set themid [lindex [split [array get NEW acid] " "]
1]<br/>  spi_exec -array C "INSERT INTO TOUCHLOG (username,tablename,recid,modtime,action,attributes) values
(''$tguser'',''$tgname'',''$themid'',''now'',''$TG_op'',''$forins'')"<br/> }<br /> return OK<br /> ' LANGUAGE
'pltcl';<br/><br /><br /> Brian Knox wrote:<br /><blockquote
cite="midPine.LNX.4.53.0305300851380.13440@tao.office.aol.com"type="cite"><pre wrap="">That's not what I was asking. I
knowI can access the values of each
 
column using NEW.columnname. What I was asking is if there's anyway to use
the NEW record to get a list of the columnnames in it without knowing them
beforehand.

Brian Knox

On Thu, 29 May 2003, George Weaver wrote:
 </pre><blockquote type="cite"><pre wrap="">Hi Brian;

Assuming "NEW" has been declared as   foo%rowtype,  you can access the
columns thus
   NEW.xxx where xxx is the column name

HTH.
George

----- Original Message -----
From: "Brian Knox" <a class="moz-txt-link-rfc2396E" href="mailto:laotse@aol.net"><laotse@aol.net></a>
To: <a class="moz-txt-link-rfc2396E" href="mailto:pgsql-sql@postgresql.org"><pgsql-sql@postgresql.org></a>
Sent: Thursday, May 29, 2003 11:11 AM
Subject: [SQL] "record" datatype - plpgsql

   </pre><blockquote type="cite"><pre wrap="">Given a variable of the "record" data type in pl/pgsql, is it possible
to
get the names of the columns ( attributes ) of that record?

eg, given record "NEW" for table "foo", is there a way to get information
concerning the columns that make up that record?

Brian Knox

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

<a class="moz-txt-link-freetext"
href="http://www.postgresql.org/docs/faqs/FAQ.html">http://www.postgresql.org/docs/faqs/FAQ.html</a>
     </pre></blockquote></blockquote><pre wrap="">
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to <a class="moz-txt-link-abbreviated"
href="mailto:majordomo@postgresql.org">majordomo@postgresql.org</a>

 </pre></blockquote><br />