Question about WITH ORDINALITY and unnest - Mailing list 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