Question about WITH ORDINALITY and unnest

From: Mike Martin <redtux1(at)gmail(dot)com>
To: pgsql-sql <pgsql-sql(at)lists(dot)postgresql(dot)org>
Subject: Question about WITH ORDINALITY and unnest
Date: 2021-01-23 13:17:09
Message-ID: CAOwYNKahL2DCvqx7E_aiWkFE50aHuK3wBwPyb-2_u+P9ObRu_A@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

Hi
I have a strange case with ordinality and row number with multiple
rows/arrays.
The row numbering is per array, rather than for the whole set. ie
I have a two row data set with
row 1 {val1,val2,val3,val4,val5}
row 2 {vala,valb}

Then the

result of

SELECT DISTINCT
array_agg(uargs ORDER BY nr),

og.transref,fileid
FROM og,UNNEST (groupargs) WITH ORDINALITY uarg(uargs,nr)
GROUP By og.transref,fileid
(og is a cte with grouping of trasref and fileid)
is

{val1,val1,val2,val2,val3,val4,val5}
or without the grouping
val1,1
vala,1
val2,2
valb,2
val3,3
val4,4
val5,5

which really messes up the ordering of the array.

Is there any way to make the ordinality ordering to be over the entire
record set rather than per array

(the idea is to combine two arrays of varying dimensions into one, keeping
order)

I have got a working solution by adding a rowid and ordering the array by
eg: rowid+nr (where rowid is 100,200, big enough not to be in record set)

thanks

Mike

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message David G. Johnston 2021-01-23 16:14:19 Re: Question about WITH ORDINALITY and unnest
Previous Message Roberto Médola 2021-01-20 12:58:22 Re: Bring to dead tuples to alive