Re: proposal: auxiliary functions for record type

On Dec11, 2010, at 16:03 , Pavel Stehule wrote:
> 2010/12/11 Florian Pflug <fgp(at)phlo(dot)org>:
>> On Dec11, 2010, at 06:20 , Pavel Stehule wrote:
>>> I wrote a few functions for record type - record_expand,
>>> record_get_fields, record_get_field, record_set_fields.
>>
>> Just FYI, I've created something similar a while ago. The code can be found at
>> https://github.com/fgp/pg_record_inspect
>>
>> The main difference seems to be that my code allows you to retrieve fields from a record without casting it to text. As a consequence, however, it lacks the set-returning functions that your's provides. It also doesn't provide a way to modify fields.
>
> Casting to text is necessary for PL/pgSQL. I am not happy from this,
> but there are not other way than using a common type - text - because
> you don't know a target type.

I use the anyarray/anyelement machinery to cheat there, at least a bit. My function fieldvalue() returns anyelement and takes a parameter <defval> of type anyelement, which serves two purposes.

First, by virtue of the anyelement machinery, the return type of fieldvalue() is that of <defval>. If the actual type of the requested field matches that type, the value is returned. If they don't match, the parameter <coerce> decided whether fieldvalue() tries to cast the value to the requested type, or simply raises an error.

Second, to also give the *value*, not only the *type* of <defval> a meaning, it serves as the default return value. If requested field contains NULL, <defvalue> is returned instead. You are, of course, free to pass NULL for <defvalue> itself to turn that mapping into a NOP.

Note that the returned value's type is always the same as <defval>'s type, so the whole thing is perfectly type-safe from the point of view of the rest of the system.

As long as you know all possible types than can appear in your record's fields, you can do in Pl/PgSQL something along the line of
declare
v_value_type1 type1;
v_value_type2 type2;
...
v_value_typeN typeN;
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;

It works pretty well for me...

best regards,
Florian Pflug