FROM function or SELECT function

When I have set returning functions and even non-set returning functions, I love putting them in the FROM clause instead of the SELECT part. I often take it for granted that the results are usually the same, but in some important situations, they are different. It's not guaranteed to be the same when your function is not immutable.

When it doesn't matter in terms of answers

If you expect the same inputs to yield the same outputs, such as with this example:

SELECT substring(f.string, i,1)
FROM (SELECT 'hello') AS f(string)
	, generate_series(1, length(f.string)) AS i;

Is equivalent to:

SELECT substring(f.string,  generate_series(1, length(f.string)),1)
FROM (SELECT 'hello') AS f(string);

I prefer using the FROM approach when all else being equal, because I can then use that FROM output in a WHERE clause. Like so:

SELECT c  
FROM (SELECT 'hello') AS f(string)
	, generate_series(1, length(f.string)) AS i, substring(f.string, i,1) AS c
WHERE mod(ascii(c) ,i ) = 1;

and at least in prior versions of PostgreSQL, calls to these functions especially ones that returned multiple columns, often caused function call duplication when it was in the SELECT part which made things slower. I don't think that is the case these days, though I haven't tested.

The convenience of being able reuse those outputs in your WHERE, is a killer feature when doing PostGIS spatial queries with all the richness of PostGIS set returning and composite returning functions. You'd have to write a lot of gyrations of ugly sub queries, if you settled with having your functions all in the SELECT clause.

Whenever you see a function in the FROM clause using arguments from prior tables preceded with a comma, it's a short-hand for writing CROSS JOIN LATERAL ..., so yes sometimes I do write the whole thing out when I'm feeling particularly patient and pedantic, like so:

SELECT c  
FROM (SELECT 'hello') AS f(string)
	CROSS JOIN LATERAL generate_series(1, length(f.string)) AS i
	CROSS JOIN LATERAL substring(f.string, i,1) AS c
WHERE mod(ascii(c) ,i ) = 1;

And some people laugh at me for wasting those extra key strokes to make a point. The point being it reminds me, I can change that to a LEFT JOIN LATERAL and achieve a slightly different purpose and is clearer to viewers of the magic that is happening.

When it matters

I love that FROM trick so much that, I often get blind-sided when the results come out different from what my mind thinks it should be. It matters when your function is VOLATILE.

Here is an example using the random() function. One might expect each call to it to give you a different number right? But here is a query that will make it not do that, cause it's only calling the function once evidentially. Which is great if that is what you wanted. Not so great when you were trying to create a manifest of 100 passengers for your futuristic voyage, only to discover all passengers names are all the same.

SELECT r
FROM generate_series(1,5), random() AS r;

Output gives you 5 repeating numbers

          r
---------------------
 0.19868264850983475
 0.19868264850983475
 0.19868264850983475
 0.19868264850983475
 0.19868264850983475
(5 rows)

Now if you write your query like so:

SELECT  random() AS r
FROM generate_series(1,5);

, you do get 5 different numbers:

         r
--------------------
 0.9913061113169994
 0.3108248186791389
 0.7519172938054108
 0.7725538387216184
 0.2287233312353214
(5 rows)