Re: Review: UNNEST (and other functions) WITH ORDINALITY

On 26 June 2013 01:22, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
> Folks,
>
> (the below was already discussed on IRC)
>
> Leaving names aside on this patch, I'm wondering about a piece of
> functionality I have with the current unnest() and with the
> unnest_ordinality()[1] extension: namely, the ability to unnest several
> arrays "in parallel" by using unnest() in the target list.
>
> For example, given the table:
>
> lotsarrays (
> id serial PK,
> arr1 int[],
> arr2 numeric[],
> arr3 boolean[]
> )
>
> I can currently do:
>
> SELECT id,
> unnest(arr1) as arr1,
> unnest(arr2) as arr2,
> unnest(arr3) as arr3
> FROM lotsarrays;
>
> ... and if arr1, 2 and 3 are exactly the same length, this creates a
> coordinated dataset. I can even use the unnest_ordinality() extension
> function to get the ordinality of this combined dataset:
>
> SELECT id,
> (unnest_ordinality(arr1)).element_number as array_index,
> unnest(arr1) as arr1,
> unnest(arr2) as arr2,
> unnest(arr3) as arr3
> FROM lotsarrays;
>
> There are reasons why this will be complicated to implement WITH
> ORDINALITY; DF, Andrew and I discussed them on IRC. So allowing WITH
> ORDINALITY in the target list is a TODO, either for later in 9.4
> development, or for 9.5.
>
> So, this isn't stopping the patch; I just want a TODO for "implement
> WITH ORDINALITY in the target list for SRFs".
>

So if I'm understanding correctly, your issue is that WITH ORDINALITY
is currently only accepted on SRFs in the FROM list, not that it isn't
working as expected in any way. I have no objection to adding a TODO
item to extend it, but note that the restriction is trivial to work
around:

CREATE TABLE lotsarrays
(
id serial primary key,
arr1 int[],
arr2 numeric[],
arr3 boolean[]
);

INSERT INTO lotsarrays(arr1, arr2, arr3) VALUES
(ARRAY[1,2], ARRAY[1.1, 2.2], ARRAY[true, false]),
(ARRAY[10,20,30], ARRAY[10.1, 20.2, 30.3], ARRAY[true, false, true]);

CREATE OR REPLACE FUNCTION unnest_ordinality(anyarray)
RETURNS TABLE(element_number bigint, element anyelement) AS
$$
SELECT ord, elt FROM unnest($1) WITH ORDINALITY AS t(elt, ord)
$$
LANGUAGE sql STRICT IMMUTABLE;

SELECT id,
(unnest_ordinality(arr1)).element_number as array_index,
unnest(arr1) as arr1,
unnest(arr2) as arr2,
unnest(arr3) as arr3
FROM lotsarrays;
id | array_index | arr1 | arr2 | arr3
----+-------------+------+------+------
1 | 1 | 1 | 1.1 | t
1 | 2 | 2 | 2.2 | f
2 | 1 | 10 | 10.1 | t
2 | 2 | 20 | 20.2 | f
2 | 3 | 30 | 30.3 | t
(5 rows)

Personally I'm not a fan of SRFs in the select list, especially not
multiple SRFs there, since the results are hard to deal with if they
return differing numbers of rows. So I would tend to write this as a
LATERAL FULL join on the ordinality columns:

SELECT id,
COALESCE(u1.ord, u2.ord, u3.ord) AS array_index,
u1.arr1, u2.arr2, u3.arr3
FROM lotsarrays,
unnest(arr1) WITH ORDINALITY AS u1(arr1, ord)
FULL JOIN unnest(arr2) WITH ORDINALITY AS u2(arr2, ord) ON u2.ord = u1.ord
FULL JOIN unnest(arr3) WITH ORDINALITY AS u3(arr3, ord) ON u3.ord =
COALESCE(u1.ord, u2.ord);

Either way, I think the WITH ORDINALITY patch is working as expected.

Regards,
Dean