Re: proposal: auxiliary functions for record type

On Dec 11, 2010, at 9:24 AM, Florian Pflug wrote:
> begin
> for v_field in select * from fieldinfos(myrec) loop
> case
> when v_field.fieldtype = 'type1'::regtype then
> v_value_type1 := fieldvalue(myrec, NULL::type1, false);
> <Do something with v_value_type1>
> ...
> when v_field.fieldtype = 'typeN'::regtype then
> v_value_typeN := fieldvalue(myrec, NULL::typeN, false);
> <Do something with v_value_typeN>
> else raise exception 'Unexpected type % in record %', v_field.fieldtype, myrec;
> end case;
> end loop;
> end;

All that casting looks horribly painful. :(

This is a case where what we really need is a data type that can accept data of any type and tell you what the type actually is. Another place that could really use that are crosstabs, though that case is more complex in that you're dealing with a recordset of fields and you don't know the type of any of them.

I wonder if it would be possible to create such a type without major backend surgery... I'm thinking the type would have a structure that stores the OID of the original type and then stores the text representation of that type. CAST functions to this type would know what the input data was and could store that information. The challenge is creating a way to cast back to the original type... I don't see a way to do that with current infrastructure.
--
Jim C. Nasby, Database Architect jim(at)nasby(dot)net
512.569.9461 (cell) http://jim.nasby.net