On Dec11, 2010, at 16:03 , Pavel Stehule wrote:
> 2010/12/11 Florian Pflug <fgp@phlo.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.
Asa consequence, however, it lacks the set-returning functions that your's provides. It also doesn't provide a way to
modifyfields.
>
> 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
takesa 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
ofthe requested field matches that type, the value is returned. If they don't match, the parameter <coerce> decided
whetherfieldvalue() 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
requestedfield contains NULL, <defvalue> is returned instead. You are, of course, free to pass NULL for <defvalue>
itselfto 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
fromthe 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
theline of
declarev_value_type1 type1;v_value_type2 type2;...v_value_typeN typeN;
beginfor 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