Re: Review: UNNEST (and other functions) WITH ORDINALITY
От | Dean Rasheed |
---|---|
Тема | Re: Review: UNNEST (and other functions) WITH ORDINALITY |
Дата | |
Msg-id | CAEZATCX+ESWLSNfLnwKpMYuLBOdhmD9bT39p=+Pz9v9opVzGsw@mail.gmail.com обсуждение исходный текст |
Ответ на | Re: Review: UNNEST (and other functions) WITH ORDINALITY (Josh Berkus <josh@agliodbs.com>) |
Список | pgsql-hackers |
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
В списке pgsql-hackers по дате отправления:
Следующее
От: Szymon GuzДата:
Сообщение: Re: [PATCH] Fix conversion for Decimal arguments in plpython functions