pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feed"record" datatype - plpgsql
8+ messages / 6 participants
[nested] [flat]
* "record" datatype - plpgsql
@ 2003-05-29 16:11 Brian Knox <laotse@aol.net>
0 siblings, 2 replies; 8+ messages in thread
From: Brian Knox @ 2003-05-29 16:11 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: "record" datatype - plpgsql
@ 2003-05-29 19:01 George Weaver <georgew1@mts.net>
parent: Brian Knox <laotse@aol.net>
1 sibling, 2 replies; 8+ messages in thread
From: George Weaver @ 2003-05-29 19:01 UTC (permalink / raw)
To: Brian Knox <laotse@aol.net>; pgsql-sql
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
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: "record" datatype - plpgsql
@ 2003-05-29 21:29 George Weaver <georgew1@mts.net>
parent: George Weaver <georgew1@mts.net>
1 sibling, 0 replies; 8+ messages in thread
From: George Weaver @ 2003-05-29 21:29 UTC (permalink / raw)
To: Brian Knox <laotse@aol.net>; +Cc: pgsql-sql
Brian,
You can also use the "record" type as well in the same way.
George
----- Original Message -----
From: "George Weaver" <georgew1@mts.net>
To: "Brian Knox" <laotse@aol.net>; <pgsql-sql@postgresql.org>
Sent: Thursday, May 29, 2003 2:01 PM
Subject: Re: [SQL] "record" datatype - plpgsql
> 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 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: "record" datatype - plpgsql
@ 2003-05-29 22:05 Stephan Szabo <sszabo@megazone23.bigpanda.com>
parent: Brian Knox <laotse@aol.net>
1 sibling, 0 replies; 8+ messages in thread
From: Stephan Szabo @ 2003-05-29 22:05 UTC (permalink / raw)
To: Brian Knox <laotse@aol.net>; +Cc: pgsql-sql
On Thu, 29 May 2003, Brian Knox wrote:
> 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?
AFAIK there isn't one without other information (like that the table was
"foo").
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: "record" datatype - plpgsql
@ 2003-05-30 12:52 Brian Knox <laotse@aol.net>
parent: George Weaver <georgew1@mts.net>
1 sibling, 2 replies; 8+ messages in thread
From: Brian Knox @ 2003-05-30 12:52 UTC (permalink / raw)
To: George Weaver <georgew1@mts.net>; +Cc: pgsql-sql
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
> >
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: "record" datatype - plpgsql
@ 2003-05-30 13:50 Tom Lane <tgl@sss.pgh.pa.us>
parent: Brian Knox <laotse@aol.net>
1 sibling, 1 reply; 8+ messages in thread
From: Tom Lane @ 2003-05-30 13:50 UTC (permalink / raw)
To: Brian Knox <laotse@aol.net>; +Cc: George Weaver <georgew1@mts.net>; pgsql-sql
Brian Knox <laotse@aol.net> writes:
> 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.
Not in plpgsql ... and if you did, you couldn't do anything useful with
the names (like access the fields) anyway. I believe you can do it in
pltcl though.
regards, tom lane
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: "record" datatype - plpgsql
@ 2003-05-30 14:38 Jr. <arcpro@digitalwizardry.net>
parent: Brian Knox <laotse@aol.net>
1 sibling, 0 replies; 8+ messages in thread
From: Jr. @ 2003-05-30 14:38 UTC (permalink / raw)
To: pgsql-sql
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
>
>
>
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: "record" datatype - plpgsql
@ 2003-05-30 17:12 Sean Chittenden <sean@chittenden.org>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 8+ messages in thread
From: Sean Chittenden @ 2003-05-30 17:12 UTC (permalink / raw)
To: Brian Knox <laotse@aol.net>; +Cc: George Weaver <georgew1@mts.net>; pgsql-sql
> > 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.
>
> Not in plpgsql ... and if you did, you couldn't do anything useful
> with the names (like access the fields) anyway. I believe you can
> do it in pltcl though.
If you're shopping around for pl languages, pl/ruby fits the bill too.
Reflective language with dynamic execution. -sc
http://moulon.inra.fr/ruby/plruby.html
--
Sean Chittenden
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2003-05-30 17:12 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-05-29 16:11 "record" datatype - plpgsql Brian Knox <laotse@aol.net>
2003-05-29 19:01 ` George Weaver <georgew1@mts.net>
2003-05-29 21:29 ` George Weaver <georgew1@mts.net>
2003-05-30 12:52 ` Brian Knox <laotse@aol.net>
2003-05-30 13:50 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-05-30 17:12 ` Sean Chittenden <sean@chittenden.org>
2003-05-30 14:38 ` Jr. <arcpro@digitalwizardry.net>
2003-05-29 22:05 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
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