On 22nd of November, Tom Lane committed patch:

Support multi-argument UNNEST(), and TABLE() syntax for multiple functions.
 
This patch adds the ability to write TABLE( function1(), function2(), ...)
as a single FROM-clause entry.  The result is the concatenation of the
first row from each function, followed by the second row from each
function, etc; with NULLs inserted if any function produces fewer rows than
others.  This is believed to be a much more useful behavior than what
Postgres currently does with multiple SRFs in a SELECT list.
 
This syntax also provides a reasonable way to combine use of column
definition lists with WITH ORDINALITY: put the column definition list
inside TABLE(), where it's clear that it doesn't control the ordinality
column as well.
 
Also implement SQL-compliant multiple-argument UNNEST(), by turning
UNNEST(a,b,c) into TABLE(unnest(a), unnest(b), unnest(c)).
 
The SQL standard specifies TABLE() with only a single function, not
multiple functions, and it seems to require an implicit UNNEST() which is
not what this patch does.  There may be something wrong with that reading
of the spec, though, because if it's right then the spec's TABLE() is just
a pointless alternative spelling of UNNEST().  After further review of
that, we might choose to adopt a different syntax for what this patch does,
but in any case this functionality seems clearly worthwhile.
 
Andrew Gierth, reviewed by Zoltán Böszörményi and Heikki Linnakangas, and
significantly revised by me

There are many things about this patch that make me happy :)

For quite some time we have unnest function. Which can be used to extract values from arrays:

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

The problem is – can we unnest several arrays at once?

We could:

select *
from
    unnest(array[1,2,3]) as unnest_1,
    unnest(array['a','b','c']) as unnest_2;
 unnest_1 | unnest_2 
----------+----------
        1 | a
        1 | b
        1 | c
        2 | a
        2 | b
        2 | c
        3 | a
        3 | b
        3 | c
(9 rows)

But the behavior is (at least for some) quite unexpected. Of course we could:

select
    unnest(array[1,2,3]) as unnest_1,
    unnest(array['a','b','c']) as unnest_2;
 unnest_1 | unnest_2 
----------+----------
        1 | a
        2 | b
        3 | c
(3 rows)

Which seems simple, but – having it in select clause makes certain things harder, and what's more – it did produce quite unexpected results in cases where number of items in arrays was not identical:

select
    unnest(array[1,2]) as unnest_1,
    unnest(array['a','b','c']) as unnest_2;
 unnest_1 | unnest_2 
----------+----------
        1 | a
        2 | b
        1 | c
        2 | a
        1 | b
        2 | c
(6 rows)

Now. The problem is gone. We can unnest multiple arrays at once, with single call to unnest:

select
    *
from
    unnest(
        array[1,2],
        array['a','b','c']
    );
 unnest | unnest 
--------+--------
      1 | a
      2 | b
 [null] | c
(3 rows)

Result of such unnest is also less surprising – if one (or more) arrays will “run out of elements" – null will be substituted.

The second part of the change (extended table() syntax) is what was used, to make it possible, but we can use it for other purposes too.

Let's assume we have two simple set-returning functions:

create function t1( OUT lang_oid INT4, OUT lang_name TEXT ) returns setof record as $$
select oid::int4, lanname::text from pg_language limit 3;
$$ language sql;
CREATE FUNCTION
 
select * from t1();
 lang_oid | lang_name 
----------+-----------
       12 | internal
       13 | c
       14 | sql
(3 rows)

And second:

create function t2( OUT table_oid INT4, OUT table_schema text, OUT table_name TEXT ) returns setof record as $$
select c.oid::int4, n.nspname::text, c.relname::text
from pg_class c
    join pg_namespace n on c.relnamespace = n.oid
where
    c.relkind = 'r'
limit 5;
$$ language sql;
CREATE FUNCTION
 
select * from t2();
 table_oid | table_schema |  table_name   
-----------+--------------+---------------
      2619 | pg_catalog   | pg_statistic
      1247 | pg_catalog   | pg_type
     16623 | public       | owocowy_rynek
      1260 | pg_catalog   | pg_authid
      1249 | pg_catalog   | pg_attribute
(5 rows)

And now you can:

select * from table( t1(), t2() );
 lang_oid | lang_name | table_oid | table_schema |  table_name   
----------+-----------+-----------+--------------+---------------
       12 | internal  |      2619 | pg_catalog   | pg_statistic
       13 | c         |      1247 | pg_catalog   | pg_type
       14 | sql       |     16623 | public       | owocowy_rynek
   [null] | [null]    |      1260 | pg_catalog   | pg_authid
   [null] | [null]    |      1249 | pg_catalog   | pg_attribute
(5 rows)

(of course showing language and table information in the same result set doesn't make sense, but it's just an example).

It's a pretty cool addition. One that (by me) will not be used very often, but it's good to know that I can now unnest multiple arrays, and get the results in reasonable way. Thanks RhodiumToad (Andrew).

  1. One comment

  2. Nov 24, 2013

    Just my little contribution when it comes to random row selection used with mysql & php. Based on the solution that consists of returning the count(*) of a table, then using that value to select a random row.SELECT FLOOR(RAND() * COUNT(*)) AS rand_row FROM foo;SELECT * FROM foo LIMIT {$rand_row}, 1; orSELECT COUNT(*) AS rows FROM foo;SELECT * FROM foo LIMIT {rand(0,$rows-1)}, 1;The problem with that solution from the MySQL standpoint is that there still remains the possibility of duplicate selections when we want more than one row, especially if the table is not that large (e.g. what are the chances of getting at least 2 duplicate rows while selecting 5 randomly, 1 at a time, out of a set of 10).My approach is to rather generate unique random numbers from php, then fetch the corresponding table rows:1- Use the appropriate php methods to fetch the table count from MySQL as done before:SELECT COUNT(*) FROM foo;2- Use php to generate some unique random numbers based on the count.This is the php function that i use. It takes 3 arguments: the minimum and maximum range values, and the amount of unique random numbers to be returned. It returns these numbers as an array.

Leave a comment