CODEWITHSUNDEEP
postgresql
Eugen Konkov
Eugen Konkov

Reputation: 25153

Why I can not refer to table when join lateral function?

I have query:

select * 
from dates d
left join (select * from range( d.start, d.stop ) ) x 
on d.start < d.stop

Which cause error:

ERROR:  invalid reference to FROM-clause entry for table "d"
LINE 3: left join (select * from range( d.start, d.stop ) ) x on d.s...
                                        ^
HINT:  There is an entry for table "d", but it cannot be referenced from this part of the query.

Reading the doc:

Table functions appearing in FROM can also be preceded by the key word LATERAL, but for functions the key word is optional; the function's arguments can contain references to columns provided by preceding FROM items in any case.

A LATERAL item can appear at top level in the FROM list, or within a JOIN tree. In the latter case it can also refer to any items that are on the left-hand side of a JOIN that it is on the right-hand side of.

Inside select * from range( d.start, d.stop ) the range is lateral.

Why I can not refer to d table?

Upvotes: 0

Views: 602

Answers (1)

user330315
user330315

Reputation:

With a derived table you need the lateral keyword:

select * 
from dates d
  left join lateral (
    select * from range( d.start, d.stop ) 
  ) x on d.start < d.stop

If you remove the unnecessary derived table (aka "sub-query") the lateral keyword is optional (assuming your "range" function is a set returning function):

select * 
from dates d
  left join range( d.start, d.stop ) as x 
on d.start < d.stop

Upvotes: 3

Related Questions