From: Jr. <arcpro@digitalwizardry.net>
To: pgsql-sql@postgresql.org
Subject: Re: "record" datatype - plpgsql
Date: Fri, 30 May 2003 10:38:36 -0400
Message-ID: <3ED76CEC.5000200@digitalwizardry.net> (raw)
References: <Pine.LNX.4.53.0305280753040.30365@tao.office.aol.com>
<005001c32624$0f0f3980$0100007f@cleartag>
<Pine.LNX.4.53.0305300851380.13440@tao.office.aol.com>
I attempted the same thing in pl/pgsql but was unable to find a
satisfactory solution using it. I eventually started using tcl as the
procedural language to get this type of effect. Tcl casts NEW and OLD
into arrays in a manner that makes it possible.
Original post:
Subject: PL/Pgsql Trigger fcuntion issue..
This is suppose to pull all the columns of the table that initiated the
trigger func from the sys catalogs, loop through them and put everything
that has changed between OLD and NEW into a comma delimited string for
input into a log like table for future analysis via middleware
(php,perl..,etc). Here is the problem, OLD.A results in 'old does not
have field A', which is true. I cant get the OLD and NEW record objects
to realize that I want OLD.<string value of A> for the column name
instead of an explicit A as the column name. The only way I can find to
make this work is by using TCL for the procedural 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 stripped to
show my specific problem so using TCL is currently not in my list of
options. Any insight will be greatly appreciated.
create or replace function hmm() returns TRIGGER as '
DECLARE
table_cols RECORD;
attribs VARCHAR;
A VARCHAR;
BEGIN
IF TG_OP = ''UPDATE'' THEN
FOR table_cols IN select attname from pg_attribute where attrelid =
TG_RELID and attnum > -1 LOOP
A := table_cols.attname;
IF OLD.A != NEW.A THEN --Begin problem
IF attribs != '''' THEN
attribs := attribs || '','' || table_cols.attname || ''='' ||
OLD.A || ''->'' || NEW.A;
ELSE
attribs := table_cols.attname || ''='' || OLD.A || ''->'' || NEW.A;
END IF;
END IF;
END LOOP;
END IF;
RAISE EXCEPTION ''%'', attribs;
RETURN NULL;
END;
' Language 'plpgsql';
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):
create function touch_loggerINSUPD() returns OPAQUE as '
spi_exec "select current_user as tguser"
spi_exec "select relname as tgname from pg_class where relfilenode =
$TG_relid"
if {[string equal -nocase $tguser audit] } {return OK }
if {[string equal -nocase $TG_op INSERT] } {
set forins ""
foreach i $TG_relatts {
set forins "$forins^[array get NEW $i]"
}
set themid [lindex [split [array get NEW acid] " "] 1]
spi_exec -array C "INSERT INTO TOUCHLOG
(username,tablename,recid,modtime,action,attributes) values
(''$tguser'',''$tgname'',''$themid'',''now'',''$TG_op'',''$forins'')"
}
if {[string equal -nocase $TG_op UPDATE] } {
set forins ""
set toadd ""
foreach i $TG_relatts {
if {[string equal -nocase [array get NEW $i] [array get OLD $i]] == 0} {
set toadd "[array get OLD $i]-[array get NEW $i]"
set forins "$forins^[string trim $toadd \']"
}
}
set themid [lindex [split [array get NEW acid] " "] 1]
spi_exec -array C "INSERT INTO TOUCHLOG
(username,tablename,recid,modtime,action,attributes) values
(''$tguser'',''$tgname'',''$themid'',''now'',''$TG_op'',''$forins'')"
}
return OK
' LANGUAGE 'pltcl';
Brian Knox wrote:
>That's not what I was asking. I know I 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:
>
>
>
>>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" <laotse@aol.net>
>>To: <pgsql-sql@postgresql.org>
>>Sent: Thursday, May 29, 2003 11:11 AM
>>Subject: [SQL] "record" datatype - plpgsql
>>
>>
>>
>>
>>>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?
>>>
>>>http://www.postgresql.org/docs/faqs/FAQ.html
>>>
>>>
>>>
>
>---------------------------(end of broadcast)---------------------------
>TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
>
>
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: arcpro@digitalwizardry.net
Subject: Re: "record" datatype - plpgsql
In-Reply-To: <3ED76CEC.5000200@digitalwizardry.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox