Home > mailing lists

Thread: Playing with 9.4devel - unnest

Playing with 9.4devel - unnest

From

Tim Kane

Date:

10 March 2014, 18:04:55

Hi all,

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?

Cheers,

Tim

Re: Playing with 9.4devel - unnest

From

Tom Lane

Date:

10 March 2014, 18:10:19

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

Re: Playing with 9.4devel - unnest

From

Tim Kane

Date:

10 March 2014, 18:29:29

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

Re: Playing with 9.4devel - unnest

From

Tim Kane

Date:

10 March 2014, 20:17:00

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

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

Re: Playing with 9.4devel - unnest

From

bricklen

Date:

10 March 2014, 20:51:20

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