Re: Bug? Function with side effects not evaluated in CTE - Mailing list pgsql-general

David Johnston <polobo@yahoo.com> writes:
> The two comparable queries are:

> A) WITH vf ( SELECT volatile_function(x) FROM generate_series(1,10) )
> SELECT * FROM vf LIMIT 1

> B) SELECT volatile_function(x) FROM generate_series(1,10) gs (x) LIMIT 1

> In (A) the relation "vf" - which is a 10-row table with the result of
> volatile_function as the only column - is limited to a single record and
> that whole row is output as-is (because of the "*")

> In (B) the relation "gs" - which is 10 rows having the result of
> generate_series as the only column - is limited to a single row and then the
> select-list project occurs against that single row (the volatile_function)

Just for the record, your interpretation of (B) is wrong.  LIMIT acts
after select-list evaluation --- try a set-returning function in the
select list to see that this is true.

            regards, tom lane