Iterating over rowtype/record fields?

Started by Bill Gribblealmost 24 years ago2 messagesgeneral
Jump to latest
#1Bill Gribble
grib@linuxdevel.com

I've looked online and in the 7.1 docs but I can't locate much
information about the RECORD type and what I can do with it from
pl/pgsql. Are there any introspection or iteration functions that would
allow me to look at all the columns in a record without explicitly
writing RECORD.column?

I'm trying to write a fairly generic pl/pgsql trigger function to log
changes to a specified set of tables into another table. So I can
easily get a NEW and OLD record, but in the generic case I don't know
what type of table they represent. I'd like to just say something like

FOR f IN table%ROWTYPE LOOP
IF OLD.f != NEW.f THEN
make_log_entry(table, NEW.rownum, f, NEW.f);
END IF
END LOOP

don't think that works tho :)

Any ideas how I can do something like the above?

Thanks,
Bill Gribble

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Bill Gribble (#1)
Re: Iterating over rowtype/record fields?

Bill Gribble <grib@linuxdevel.com> writes:

I'm trying to write a fairly generic pl/pgsql trigger function to log
changes to a specified set of tables into another table.

I'd recommend doing it in C ... the flexibility is there, and the
performance will be better too. There are several examples of C-coded
triggers in contrib:

contrib/fulltextindex/fti.c
contrib/lo/lo.c
contrib/noupdate/noup.c
contrib/spi/autoinc.c
contrib/spi/insert_username.c
contrib/spi/moddatetime.c
contrib/spi/refint.c
contrib/spi/timetravel.c
contrib/tsearch/txtidx.c

Some of these are probably very close to what you want already.

regards, tom lane