Hi
I'm currently working on a project where we need to build a global cache
table containing all values of certain types found in any of the other
tables. Currently, a seperate insert, update and delete (plpgsql)
trigger function exists for each table in the database which is
auto-generated by a (plpgsql) function which queries the system catalogs
to find all fields with a certain type, and then generates the
appropriate plpgsql function using EXECUTE '...'.
I'd like to replace this function-generating function by a generic
trigger function that works for all tables. Due to the lack of any way
to inspect the *structure* of a record type, however, I'd have to use a
C language function for that, which induces quite some maintenance
headaches (especially if deployed on windows).
I'm therefore thinking about implementing the following generate-purpose
inspection functions for row types
record_length(record) returns smallint Returns the number of fields in the given record.
record_names(record) returns name[] Returns the names of the record's fields. Array will contain NULLs if one or more
fieldsare unnamed.
record_types(record) returns regtype[]; Returns the OIDs of the record's types. Array won't contain NULLs
record_value(record, name, anyelement) returns anyelement Returns the value of a certain (named) field. The type of
thethird argument defines the return type (its value is ignored). The field's value is cast to that type if possible,
otherwisean error is raised.
record_value(record, smallint, anyelement) returns anyelement Returns the value of the field at the given position.
record_values(record, regtype, anyelement) returns anyarray Returns an array of all values of all fields with the
giventype or whose type is a domain over the given type. No casting is done.
Any comment/critique is appreciated.
Would anyone else find those functions useful?
best regards,
Florian Pflug
