Hi
I've completed a (first) working version of a extension that allows
easier introspection of composite types from SQL and pl/PGSQL.
The original proposal and ensuing discussion can be found here:
http://archives.postgresql.org/pgsql-hackers/2009-11/msg00695.php
The extension can be found on:
http://github.com/fgp/pg_record_inspect
This is what the extension currently provides (all in schema
record_inspect).
* fieldinfo [composite type]
Used to by fieldinfos() to describe a record's fields.
Contains the fields
fieldname (name),
fieldtype (regclass),
fieldtypemod (varchar)
* fieldinfo[] fieldinfos(record)
Returns an array of <fieldinfo>s describing the record''s fields
* anyelement fieldvalue(record, field name, defval anyelement,
coerce boolean)
Returns the value of the field <field>, or <defval> should the value
be null. If <coerce> is true, the value is coerced to <defval>'s type
if possible, otherwise an error is raised if the field''s type and
<defval>'s type differ.
* anyelement fieldvalues(record, defval anyelement, coerce boolean)
Returns an array containing values of the record'' fields. NULL
values are replaced by <defval>. If <coerce> is false, only the
fields with the same type as <defval> are considered. Otherwise, the
field'' values are coerced if possible, or an error is raised if not.
The most hacky part of the code is probably coerceDatum() - needed to
coerce a field's value to the requested output type. I wanted to avoid
creating and parsing an actual SQL statement for every cast, and instead
chose to use coerce_to_target_type() to create the expression trees
representing casts. I use the noe type CoerceToDomainValue to inject the
source value into the cast plan upon execution - see makeCastPlan() and
execCastPlan() for details. If anyone has a better idea, please speak up....
I personally would like to see this becoming a contrib module one day,
but that of course depends on how much interest there is in such a feature.
best regards,
Florian Pflug