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 <[email protected]>
Date: Monday, 10 March 2014 15:26
To: Tom Lane <[email protected]>
Cc: pgsql-general General <[email protected]>
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 <[email protected]>
Date: Monday, 10 March 2014 15:10
To: Tim Kane <[email protected]>
Cc: pgsql-general General <[email protected]>
Subject: Re: [GENERAL] Playing with 9.4devel - unnest
Tim Kane <[email protected]> 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
