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
smime.p7s
Description: S/MIME Cryptographic Signature
