Re: Inspection of row types in pl/pgsql and pl/sql

Hello

new hstore has a very nice interface for record field iteration

http://okbob.blogspot.com/2009/10/dynamic-access-to-record-fields-in.html

Regards
Pavel Stehule

2009/11/13 Florian G. Pflug <fgp(at)phlo(dot)org>:
> 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 fields are 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 the third
>  argument defines the return type (its value is ignored). The
>  field's value is cast to that type if possible, otherwise an
>  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 given type 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
>
>