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

On 26 June 2013 01:22, Josh Berkus <josh@agliodbs.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
FULLJOIN 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