On 05/21/2011 03:13 AM, Karl Koster wrote:
I have a couple of questions regarding unnest.
1) If I issue a select statement "select unnest(vector1) as v from
some_table", I cannot seem to use the column alias v in a WHERE or
HAVING clause. I can use it in an ORDER BY or GROUP by clause. Is this
the way it is supposed to work?
Yes, and it's what the SQL standard requires. Otherwise, how would this
query work?
SELECT a/b FROM sometable WHERE b <> 0;
?
The SELECT list has to be processed only once the database has already
decided which rows it applies to and how.
Use unnest in a FROM clause, eg:
SELECT v1.* FROM unnest(vector) ...
This may require a join and/or subquery to obtain 'vector'.
2) If I issue a select statement "select unnest(vector1) as v1,
unnest(vector2) as v2 from some_table" and vector1 has a length of 3 and
vector2 has a length of 4, the result set will have 12 rows with the
data of vector1 repeating 4 times and vector2 repeating 3 times.
Shouldn't the content of the shorter array(s) simply be return null in
it's respective column and the result set be the size of the longest array?
unnest is a set-returning function, and it doesn't really make that much
sense to have them in the SELECT list anyway. Few databases support it,
and PostgreSQL's behavior is a historical quirk that I think most people
here hope will go quietly away at some point.
Use unnest in a FROM clause.
--
Craig Ringer
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
