1

Using Postgres 9.3, I found out that I can perform something like this:

SELECT generate_series(1,10);

But I can't do this:

SELECT (SELECT generate_series(1,10));

Can I somehow cast SELECT result to setof int to use it same as result from generate_series()?

What exactly is happening there why I can use result from function but not from SELECT?

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Logman
  • 4,031
  • 1
  • 23
  • 35
  • Came up with the crazy idea: `SELECT UNNEST(ARRAY(SELECT generate_series(1,10)));` Someone has better solution? – Logman Mar 27 '15 at 16:27

1 Answers1

4

Your first form is a non-standard feature of Postgres. It allows SRF (Set Returning Functions) in the SELECT list, which are expanded to multiple rows:

Note: that's working for functions, not for sub-selects. That's why your second SELECT is simply invalid syntax.

Standard SQL does not have a provision for that at all, so the feature is frowned upon by some and clean alternatives have been provided (thanks to improvements in the SQL standard). It is largely superseded by the LATERAL feature in Postgres 9.3+:

The simple form can be replaced by:

SELECT g
FROM   generate_series(1,10) g;

Whenever possible move SRF to the FROM clause and treat them like tables - since version 9.3 that's almost always possible.

Note that g serves as table and column alias automatically in the example. g in SELECT g binds to a column name first. More explicit syntax:

SELECT g
FROM   generate_series(1,10) AS t(g);  -- table_alias(column_alias)

You need to understand the difference between a row, a set of rows (~ a table) and an array. This would give you an array of integer:

SELECT ARRAY(SELECT g FROM generate_series(1,10) g) AS g_arr;

Browse the tags and for many related answers with code examples.

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • But how result from SELECT is different from result from generate_series? – Logman Mar 26 '15 at 02:29
  • @Logman: Postgres has the non-standard feature to allow SRF (functions!) in the `SELECT` list (which is currently being phased out and replaced by standard-conforming syntax). It does not allow (and never has allowed) sub-selects in the `SELECT` list, though, that return more than one row or more than one column. – Erwin Brandstetter Mar 26 '15 at 02:47
  • I know that you want to convince me to use sets after FROM and I am actually using it like that. But for academic reasons I would like to add this specific functions functionality to SELECT. Is it somehow possible? – Logman Mar 26 '15 at 10:46
  • @Logman: No. As I explain above: In the `SELECT` list, a set-returning *function* can return multiple rows with a single value each, but a *sub-select* can only return a *single* value. – Erwin Brandstetter Mar 26 '15 at 21:06