How to cast entity to set in PostgreSQL

Question

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?

Answer 1

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:

• Is there something like a zip() function in PostgreSQL that combines two arrays?

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+:

• What is the difference between LATERAL and a subquery in PostgreSQL?

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 generate-series and set-returning-functions for many related answers with code examples.