Re: proposal: row_to_array function

On 2 April 2015 at 01:59, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:
> On Sun, Mar 29, 2015 at 1:27 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> writes:
>>> here is rebased patch.
>>> It contains both patches - row_to_array function and foreach array support.
>>
>> While I don't have a problem with hstore_to_array, I don't think that
>> row_to_array is a very good idea; it's basically encouraging people to
>> throw away SQL datatypes altogether and imagine that everything is text.
>> They've already bought into that concept if they are using hstore or
>> json, so smashing elements of those containers to text is not a problem.
>> But that doesn't make this version a good thing.
>>
>> (In any case, those who insist can get there through row_to_json, no?)
>
> You have a point. What does attached do that to_json does not do
> besides completely discard type information? Our json api is pretty
> rich and getting richer. For better or ill, we dumped all json
> support into the already stupendously bloated public namespace and so
> it's always available.

I can see plenty of utility for a function like Pavel speaks of, but
I'd personally rather see it as a function that returns table (colname
name, coltype regtype, coltypmod integer, coltextvalue text,
colordinal integer) so it can carry more complete information and
there's no need to worry about foreach(array). The main use of a
function that includes text representations of the values would IMO be
using it from plain SQL, rather than PL/PgSQL, when faced with
anonymous records.

I'd find it more useful to have lvalue-expressions for dynamic access
to record fields and a function to get record metadata - field names,
types and typmods. Some kind of "pg_get_record_info(record) returns
table(fieldname text, fieldtype regtype, fieldtypmod integer)" and a
PL/PgSQL lvalue-expression for record field access like
"RECORD_FIELD(therecord, fieldname)". I would _certainly_ want to be
able to get the type metadata without the values.

That way you could interact natively with the fields in their true
types, without forcing conversion into and out of 'text', which is a
known performance pain-point with PL/PgSQL. (PL/PgSQL doesn't have a
VARIANT type or support for using 'anyelement', which would be the
other way to solve the type flattening problem IMO).

Think:

DECLARE
myrow record;
fi record;
BEGIN
EXECUTE user_supplied_dynamic_query INTO myrow;
FOR fi IN
SELECT fieldname, fieldtype, fieldtypmod
FROM pg_get_record_info(myrow)
LOOP
IF fi.fieldtype == 'int4'::regtype THEN
RECORD_FIELD(myrow, fi.fieldname) := RECORD_FIELD(myrow,
fi.fieldname) + 1;
END IF;
END LOOP;
END;

OK, so it's a stupid example - increment all int4 fields by one. It
conveys the rough idea though - native use of the field types.

Note that RECORD_FIELD is distinct from the existing support for

EXECUTE format('SELECT $1.%I', fieldname) USING therecord;

in that that approach doesn't work for all ways that a record can be
produced, it's slow, it doesn't have a good way to enumerate field
names, and there's no equivalent to write to the field. Current
approaches for that are ghastly:
http://stackoverflow.com/q/7711432/398670 .

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services