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).
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.