</indexterm>

<para>

Subqueriesand table functionsappearing in <literal>FROM</> can be

Subqueries appearing in <literal>FROM</> can be

preceded by the key word <literal>LATERAL</>. This allows them to

reference columns provided by preceding <literal>FROM</> items.

(Without <literal>LATERAL</literal>, each<literal>FROM</> item is

(Without <literal>LATERAL</literal>, eachsubquery is

evaluated independently and so cannot cross-reference any other

<literal>FROM</> item.)

</para>

<para>

Table functions appearing in <literal>FROM</> can also be

preceded by the key word <literal>LATERAL</>, but for functions the

key word is optional; the function's arguments can contain references

to columns provided by preceding <literal>FROM</> items in any case.

</para>

<para>

A <literal>LATERAL</literal> item can appear at top level in the

<literal>FROM</> list, or within a <literal>JOIN</> tree; in the latter

<literal>FROM</> list, or within a <literal>JOIN</> tree. In the latter

case it can also refer to any items that are on the left-hand side of a

<literal>JOIN</> that it is on the right-hand side of.

</para>

polygons p2 CROSS JOIN LATERAL vertices(p2.poly) v2

WHERE (v1 &lt;-&gt; v2) &lt; 10 AND p1.id != p2.id;

</programlisting>

or in several other equivalent formulations.

or in several other equivalent formulations. (As already mentioned,

the <literal>LATERAL</> key word is unnecessary in this example, but

we use it for clarity.)

</para>

<para>

<varlistentry>

<term><literal>LATERAL</literal></term>

<listitem>

<para>The <literal>LATERAL</literal> key word can precede a

sub-<command>SELECT</command> or function-call <literal>FROM</>

item. This allows the sub-<command>SELECT</command> or function

expression to refer to columns of <literal>FROM</> items that appear

before it in the <literal>FROM</> list. (Without

<literal>LATERAL</literal>, each <literal>FROM</> item is evaluated

independently and so cannot cross-reference any other

<literal>FROM</> item.) A <literal>LATERAL</literal> item can

appear at top level in the <literal>FROM</> list, or within a

<literal>JOIN</> tree; in the latter case it can also refer to any

items that are on the left-hand side of a <literal>JOIN</> that it is

on the right-hand side of.

<para>

The <literal>LATERAL</literal> key word can precede a

sub-<command>SELECT</command> <literal>FROM</> item. This allows the

sub-<command>SELECT</command> to refer to columns of <literal>FROM</>

items that appear before it in the <literal>FROM</> list. (Without

<literal>LATERAL</literal>, each sub-<command>SELECT</command> is

evaluated independently and so cannot cross-reference any other

<literal>FROM</> item.)

</para>

<para>

<literal>LATERAL</literal> can also precede a function-call

<literal>FROM</> item, but in this case it is a noise word, because

the function expression can refer to earlier <literal>FROM</> items

in any case.

</para>

<para>

A <literal>LATERAL</literal> item can appear at top level in the

<literal>FROM</> list, or within a <literal>JOIN</> tree. In the

latter case it can also refer to any items that are on the left-hand

side of a <literal>JOIN</> that it is on the right-hand side of.

</para>

<para>

sub-<command>SELECT</command>; that is, the syntax

<literal>FROM <replaceable>func</>(...) <replaceable>alias</></literal>

is approximately equivalent to

<literal>FROM (SELECT <replaceable>func</>(...)) <replaceable>alias</></literal>.

<literal>FROM LATERAL (SELECT <replaceable>func</>(...)) <replaceable>alias</></literal>.

Note that <literal>LATERAL</> is considered to be implicit; this is

because the standard requires <literal>LATERAL</> semantics for an

<literal>UNNEST()</> item in <literal>FROM</>.

<productname>PostgreSQL</productname> treats <literal>UNNEST()</> the

same as other set-returning functions.

</para>

</refsect2>

{

Node*funcexpr;

char*funcname;

boolis_lateral;

RangeTblEntry*rte;

funcname=FigureColname(r->funccallnode);

Assert(!pstate->p_lateral_active);

pstate->p_lateral_active=r->lateral;

pstate->p_lateral_active=true;

assign_expr_collations(pstate,funcexpr);

is_lateral=r->lateral||contain_vars_of_level(funcexpr,0);

rte=addRangeTableEntryForFunction(pstate,funcname,funcexpr,

r,r->lateral, true);

r,is_lateral, true);

4567890123456789 | -4567890123456789 | 4567890123456789

(5 rows)

-- lateralSRF

-- lateralwith function in FROM

select count(*) from tenk1 a, lateral generate_series(1,two) g;

count

-------

-> Function Scan on generate_series g

(4 rows)

-- don't need the explicit LATERAL keyword for functions

explain (costs off)

select count(*) from tenk1 a, generate_series(1,two) g;

QUERY PLAN

------------------------------------------------

Aggregate

-> Nested Loop

-> Seq Scan on tenk1 a

-> Function Scan on generate_series g

(4 rows)

-- lateral with UNION ALL subselect

explain (costs off)

select * from generate_series(100,200) g,

(26 rows)

-- test some error cases where LATERAL should have been used but wasn't

select f1,g from int4_tbl a,generate_series(0, f1) g;

select f1,g from int4_tbl a,(select f1 as g) ss;

ERROR: column "f1" does not exist

LINE 1: select f1,g from int4_tbl a,generate_series(0, f1) g;

^

LINE 1: select f1,g from int4_tbl a,(select f1 as g) ss;

^

HINT: There is a column named "f1" in table "a", but it cannot be referenced from this part of the query.

select f1,g from int4_tbl a,generate_series(0, a.f1) g;

select f1,g from int4_tbl a,(select a.f1 as g) ss;

ERROR: invalid reference to FROM-clause entry for table "a"

LINE 1: select f1,g from int4_tbl a,generate_series(0, a.f1) g;

^

LINE 1: select f1,g from int4_tbl a,(select a.f1 as g) ss;

^

HINT: There is an entry for table "a", but it cannot be referenced from this part of the query.

select f1,g from int4_tbl a cross joingenerate_series(0, f1) g;

select f1,g from int4_tbl a cross join(select f1 as g) ss;

ERROR: column "f1" does not exist

LINE 1:...ct f1,g from int4_tbl a cross joingenerate_series(0, f1) g;

^

LINE 1:select f1,g from int4_tbl a cross join(select f1 as g) ss;

^

HINT: There is a column named "f1" in table "a", but it cannot be referenced from this part of the query.

select f1,g from int4_tbl a cross joingenerate_series(0, a.f1) g;

select f1,g from int4_tbl a cross join(select a.f1 as g) ss;

ERROR: invalid reference to FROM-clause entry for table "a"

LINE 1:... f1,g from int4_tbl a cross joingenerate_series(0, a.f1) g;

^

LINE 1:select f1,g from int4_tbl a cross join(select a.f1 as g) ss...

^

HINT: There is an entry for table "a", but it cannot be referenced from this part of the query.

-- SQL:2008 says the left table is in scope but illegal to access here

select f1,g from int4_tbl a right join lateral generate_series(0, a.f1) g on true;

INSERT INTO foo2 VALUES(2, 22);

INSERT INTO foo2 VALUES(1, 111);

CREATE FUNCTION foot(int) returns setof foo2 as 'SELECT * FROM foo2 WHERE fooid = $1;' LANGUAGE SQL;

--supposed to failwithERROR

--functionwithimplicit LATERAL

select * from foo2, foot(foo2.fooid) z where foo2.f2 = z.f2;

ERROR: invalid reference to FROM-clause entry for table "foo2"

LINE 1: select * from foo2, foot(foo2.fooid) z where foo2.f2 = z.f2;

^

HINT: There is an entry for table "foo2", but it cannot be referenced from this part of the query.

fooid | f2 | fooid | f2

-------+-----+-------+-----

1 | 11 | 1 | 11

2 | 22 | 2 | 22

1 | 111 | 1 | 111

(3 rows)

-- function in subselect

select * from foo2 where f2 in (select f2 from foot(foo2.fooid) z where z.fooid = foo2.fooid) ORDER BY 1,2;

fooid | f2

select*, (select rfrom (select q1as q2) x, (select q2as r) y)from int8_tbl;

select*, (select rfrom (select q1as q2) x, lateral (select q2as r) y)from int8_tbl;

selectcount(*)from tenk1 a, lateral generate_series(1,two) g;

explain (costs off)

selectcount(*)from tenk1 a, lateral generate_series(1,two) g;

explain (costs off)

selectcount(*)from tenk1 across join lateral generate_series(1,two) g;

explain (costs off)

selectcount(*)from tenk1 a, generate_series(1,two) g;

explain (costs off)

lateral (selectss2.y) ss3;

select f1,gfrom int4_tbl a,generate_series(0, f1) g;

select f1,gfrom int4_tbl a,generate_series(0,a.f1) g;

select f1,gfrom int4_tbl across joingenerate_series(0, f1) g;

select f1,gfrom int4_tbl across joingenerate_series(0,a.f1) g;

select f1,gfrom int4_tbl a,(select f1as g) ss;

select f1,gfrom int4_tbl a,(selecta.f1as g) ss;

select f1,gfrom int4_tbl across join(select f1as g) ss;

select f1,gfrom int4_tbl across join(selecta.f1as g) ss;

select f1,gfrom int4_tbl aright join lateral generate_series(0,a.f1) gon true;

select f1,gfrom int4_tbl a fulljoin lateral generate_series(0,a.f1) gon true;

CREATEFUNCTIONfoot(int) returns setof foo2as'SELECT * FROM foo2 WHERE fooid = $1;' LANGUAGE SQL;

select*from foo2, foot(foo2.fooid) zwherefoo2.f2=z.f2;