Postgres Pro
Downloads
Home   >   mailing lists

Thread: unnest

unnest

From
"John Hansen"
Date:
Attached, array -> rows iterator.

select * from unnest(array[1,2,3,4,5]);

Unnest
---------------
 1
 2
 3
 4
 5
5 rows

The switch statement could probably be done in a different way, but
there doesn't seem to be any good examples of how to return anyitem. If
anyone have a better way, please let me know.

Does anyone know how to check individual array elements for NULL values?
PG_ARG_ISNULL() seems to return true if ANY array element is null; ex::
array[1,2,3,null,4,5]

Comments / improvements welcome.

Kind regards,

John
Attachment

Re: unnest

From
Kris Jurka
Date:

On Fri, 5 Nov 2004, John Hansen wrote:

> Does anyone know how to check individual array elements for NULL values?
> PG_ARG_ISNULL() seems to return true if ANY array element is null; ex::
> array[1,2,3,null,4,5]

Arrays cannot store NULL elements, check your above statement and see that 
the whole thing is NULL when you introduce a NULL element:

# select array[1,2,3,null,4,5];array 
-------
(1 row)

or

# select array[1,2,3,null,4,5] IS NULL;?column? 
----------t
(1 row)



Kris Jurka

Re: unnest

From
Eric B.Ridge
Date:
On Nov 5, 2004, at 7:09 AM, John Hansen wrote:

> Attached, array -> rows iterator.
>
> select * from unnest(array[1,2,3,4,5]);

This is really handy!  But there is a problem...

> The switch statement could probably be done in a different way, but
> there doesn't seem to be any good examples of how to return anyitem. If
> anyone have a better way, please let me know.

Why do you need the switch statement at all? array->elements is already 
an array of Datums.  Won't simply returningarray->elements[array->i]
work?

The problem is:
test=# select * from unnest('{1,2,3,4,5}'::int8[]);  unnest
---------- 25314880 25314888 25314896 25314904 25314912
(5 rows)

Whereas simply returning the current Datum in array->elements returns 
the correct result:
    if (array->i < array->num_elements)SRF_RETURN_NEXT(funcctx,array->elements[array->i++]);    else
SRF_RETURN_DONE(funcctx);

test=# select * from unnest('{1,2,3,4,5}'::int8[]); unnest
--------      1      2      3      4      5
(5 rows)

Also works for the few other datatypes I checked.

Am I missing something obvious?

eric

Re: unnest

From
Gavin Sherry
Date:
On Fri, 5 Nov 2004, John Hansen wrote:

> Attached, array -> rows iterator.
>
> select * from unnest(array[1,2,3,4,5]);
>
> Unnest
> ---------------
>  1
>  2
>  3
>  4
>  5
> 5 rows

This mechanism is actually designed for the multiset data type in SQL.
AFAICT, our elementary one dimensional array handling mimics SQL
multisets. Is there any intention to bring this into line with the spec or
would that be mere pedantism?

Thanks,

Gavin

Re: unnest

From
John Hansen
Date:
> > The switch statement could probably be done in a different way, but
> > there doesn't seem to be any good examples of how to return anyitem. If
> > anyone have a better way, please let me know.
>
> Why do you need the switch statement at all? array->elements is already
> an array of Datums.  Won't simply returning
>     array->elements[array->i]
> work?

yea,. sorry,. worked it out shortly after posting this, but forgot to
repost.... so here it is... attached.

> The problem is:
> test=# select * from unnest('{1,2,3,4,5}'::int8[]);
>    unnest
> ----------
>   25314880
>   25314888
>   25314896
>   25314904
>   25314912
> (5 rows)
Attachment

Re: unnest

From
Bruce Momjian
Date:
I assume this is not something for our PostgreSQL CVS, even the later
SRF implementation.

---------------------------------------------------------------------------

John Hansen wrote:
> Attached, array -> rows iterator.
> 
> select * from unnest(array[1,2,3,4,5]);
> 
> Unnest
> ---------------
>  1
>  2
>  3
>  4
>  5
> 5 rows
> 
> The switch statement could probably be done in a different way, but
> there doesn't seem to be any good examples of how to return anyitem. If
> anyone have a better way, please let me know.
> 
> Does anyone know how to check individual array elements for NULL values?
> PG_ARG_ISNULL() seems to return true if ANY array element is null; ex::
> array[1,2,3,null,4,5]
> 
> Comments / improvements welcome.
> 
> Kind regards,
> 
> John
> 

Content-Description: Makefile

[ Attachment, skipping... ]

Content-Description: unnest.c

[ Attachment, skipping... ]

Content-Description: unnest.sql

[ Attachment, skipping... ]

> 
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073

Re: unnest

From
Joe Conway
Date:
Bruce Momjian wrote:
> I assume this is not something for our PostgreSQL CVS, even the later
> SRF implementation.

I agree with that assessment, at least in its present state. For example:

regression=# select * from unnest(array[[1,2,3],[4,5,6]]); unnest
--------      1      2      3      4      5      6
(6 rows)

Per SQL99 I think that ought to return something like:

-- output faked
regression=# select * from unnest(array[[1,2,3],[4,5,6]]); unnest
-------- {1,2,3} {4,5,6}
(2 rows)

Problem is that a polymorphic SRF cannot (currently at least) both 
accept and return type anyarray.

Joe

Re: unnest

From
Tom Lane
Date:
Joe Conway <mail@joeconway.com> writes:
> Problem is that a polymorphic SRF cannot (currently at least) both 
> accept and return type anyarray.

Beyond that, would the proposed function really be SQL-compliant other
than this one point?  I had the idea that UNNEST required some
fundamental changes (but I might be confusing it with something else).
        regards, tom lane