Playing with 9.4devel - unnest

Tim Kane <tim.kane@gmail.com> writes:

I decided to have a play with postgresql-9.4devel as I wanted to explore the
functionality provided by
unnest (anyarray, anyarray [, …])
I’ve taken the nightly snpahost, compiled, installed.. All good. (Obtained
from http://ftp.postgresql.org/pub/snapshot/dev/ )
However it seems the expected multi-argument unnest function doesn’t exist.

Have I missed something?

It's there:

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

If you were expecting this to change, it didn't:

regression=# \df unnest
List of functions
Schema | Name | Result data type | Argument data types | Type
------------+--------+------------------+---------------------+--------
pg_catalog | unnest | SETOF anyelement | anyarray | normal
(1 row)

because the new functionality arises from a parser transformation,
not from a simple function.

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Hmm. So it is.
My bad, thanks Tom.

I hadn’t noticed the documentation where it clearly says "This is only
allowed in the FROM clause”

xml_test=# select unnest(*) from (select array[1,2],array[1,2,3]) foo;
ERROR: function unnest() does not exist

And, yes.. I was expecting the function signature to change. Thanks for
setting me straight.

Tim

From: Tom Lane <tgl@sss.pgh.pa.us>
Date: Monday, 10 March 2014 15:10
To: Tim Kane <tim.kane@gmail.com>
Cc: pgsql-general General <pgsql-general@postgresql.org>
Subject: Re: [GENERAL] Playing with 9.4devel - unnest

Tim Kane <tim.kane@gmail.com> writes:

I decided to have a play with postgresql-9.4devel as I wanted to explore the
functionality provided by
unnest (anyarray, anyarray [, ╜])
Iâ•˙ve taken the nightly snpahost, compiled, installed.. All good. (Obtained
from http://ftp.postgresql.org/pub/snapshot/dev/ )
However it seems the expected multi-argument unnest function doesnâ•˙t exist.

Have I missed something?

It's there:

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

If you were expecting this to change, it didn't:

regression=# \df unnest
List of functions
Schema | Name | Result data type | Argument data types | Type
------------+--------+------------------+---------------------+--------
pg_catalog | unnest | SETOF anyelement | anyarray | normal
(1 row)

because the new functionality arises from a parser transformation,
not from a simple function.

regards, tom lane

I think I may have misunderstood the use case of this..

I can do the following:

select * from unnest(array[1,2,3], array[1,3,4]);
unnest | unnest
--------+--------
1 | 1
2 | 3
3 | 4
(3 rows)

But what I really wanted to do, is unnest multiple sets of array values as
returned from a table/query..

Eg:

xml_test=# create temp table z (foo integer[], bar integer[]);
CREATE TABLE

xml_test=# insert into z values (array[1,2,3], array[4,5,6]);
INSERT 0 1

xml_test=# select * from z;
foo | bar
---------+---------
{1,2,3} | {4,5,6}
(1 row)

xml_test=# select * from unnest (select foo, bar from z);
ERROR: syntax error at or near "select"
LINE 1: select * from unnest (select foo, bar from z);
^

xml_test=# select * from unnest (select * from z);
ERROR: syntax error at or near "select"
LINE 1: select * from unnest (select * from z);
^

xml_test=# select (array[1,2,3], array[4,5,6]);
row
-----------------------
("{1,2,3}","{4,5,6}")
(1 row)

xml_test=# select row(foo,bar) from z;
row
-----------------------
("{1,2,3}","{4,5,6}")
(1 row)

xml_test=# select * from unnest(array[1,2,3], array[1,3,4]);
unnest | unnest
--------+--------
1 | 1
2 | 3
3 | 4
(3 rows)

xml_test=# select * from unnest ( select row(foo,bar) from z );
ERROR: syntax error at or near "select"
LINE 1: select * from unnest ( select row(foo,bar) from z );
^

xml_test=# select * from unnest ( (select row(foo,bar) from z) );
ERROR: function unnest(record) does not exist
LINE 1: select * from unnest ( (select row(foo,bar) from z) );
^
HINT: No function matches the given name and argument types. You might need
to add explicit type casts.

Any suggestions? Or should the parser be allowing a subquery as a parameter
to unnest?

Tim

From: Tim Kane <tim.kane@gmail.com>
Date: Monday, 10 March 2014 15:26
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-general General <pgsql-general@postgresql.org>
Subject: Re: [GENERAL] Playing with 9.4devel - unnest

Hmm. So it is.
My bad, thanks Tom.

I hadn’t noticed the documentation where it clearly says "This is only
allowed in the FROM clause”

xml_test=# select unnest(*) from (select array[1,2],array[1,2,3]) foo;
ERROR: function unnest() does not exist

And, yes.. I was expecting the function signature to change. Thanks for
setting me straight.

Tim

From: Tom Lane <tgl@sss.pgh.pa.us>
Date: Monday, 10 March 2014 15:10
To: Tim Kane <tim.kane@gmail.com>
Cc: pgsql-general General <pgsql-general@postgresql.org>
Subject: Re: [GENERAL] Playing with 9.4devel - unnest

Tim Kane <tim.kane@gmail.com> writes:

I decided to have a play with postgresql-9.4devel as I wanted to explore the
functionality provided by
unnest (anyarray, anyarray [, ╜])
Iâ•˙ve taken the nightly snpahost, compiled, installed.. All good. (Obtained
from http://ftp.postgresql.org/pub/snapshot/dev/ )
However it seems the expected multi-argument unnest function doesnâ•˙t exist.

Have I missed something?

It's there:

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

If you were expecting this to change, it didn't:

regression=# \df unnest
List of functions
Schema | Name | Result data type | Argument data types | Type
------------+--------+------------------+---------------------+--------
pg_catalog | unnest | SETOF anyelement | anyarray | normal
(1 row)

because the new functionality arises from a parser transformation,
not from a simple function.

regards, tom lane

On Mon, Mar 10, 2014 at 10:16 AM, Tim Kane <tim.kane@gmail.com> wrote:

But what I really wanted to do, is unnest multiple sets of array values as
returned from a table/query..

Craig Ringer posted an interesting answer to a somewhat related question a
few months ago on Stack Overflow: http://stackoverflow.com/a/17646605