Re: unnest

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

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

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 returning
array->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

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

> > 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)

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(at)postgresql(dot)org)

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

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

Joe Conway <mail(at)joeconway(dot)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