Michael Paquier wrote:
> Hi all,
>
> Today while playing with some queries I bumped into the following thing:
> =# with count_query as (select generate_series(0,1) as a) select b
> from count_query, generate_series(1, count_query.a) as b;
> b
> ---
> 1
> (1 row)
> =# with count_query as (select generate_series(0,1) as a) select b
> from generate_series(1, count_query.a) as b, count_query;
> ERROR: 42P01: missing FROM-clause entry for table "count_query"
> LINE 1: ...eries(0,1) as a) select b from generate_series(1, count_quer...
> ^
> LOCATION: errorMissingRTE, parse_relation.c:2850
>
> I have been a little bit surprised by the fact that different entry
> ordering in the FROM clause of the main query had different effects.
> Perhaps there is something I am missing?
This seems natural to me -- in your second example, by the time you
reference count_query it hasn't yet been declared and thus it's not
available in the namespace. This is how I expect a LATERAL reference to
work: a RTE can reference previous entries, but not ones that come
later.
(SRFs in FROM become lateral references automatically, as I recall.
Without LATERAL, you wouldn't have been able to refer to count_query at
all.)
--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services