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@xxxxxxxxx> Date: Monday, 10 March 2014 15:26 To: Tom Lane <tgl@xxxxxxxxxxxxx> Cc: pgsql-general General <pgsql-general@xxxxxxxxxxxxxx> Subject: Re: 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@xxxxxxxxxxxxx> Date: Monday, 10 March 2014 15:10 To: Tim Kane <tim.kane@xxxxxxxxx> Cc: pgsql-general General <pgsql-general@xxxxxxxxxxxxxx> Subject: Re: Playing with 9.4devel - unnest Tim Kane <tim.kane@xxxxxxxxx> writes:
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 |
