If LATERAL is optional for table-valued functions, then why does this query error without it?
4
votes
2
answers
440
views
# Setup
CREATE TABLE persons
(
person_id int not null,
name TEXT
);
INSERT INTO persons VALUES
(1, 'Adam'),
(2, 'Paul'),
(3, 'Tye'),
(4, 'Sarah');
CREATE TABLE json_to_parse
(
person_id int not null,
block json
);
INSERT INTO json_to_parse VALUES
(1, '{"size": "small", "love": "x"}'),
(2, '{"size": "medium", "love": "xx"}'),
(3, '{"size": "big", "love": "xxx"}');
# The Error
This runs without issue
SELECT
*
FROM
json_to_parse
CROSS JOIN LATERAL
json_to_record(json_to_parse.block) AS my_json(size TEXT, love TEXT)
INNER JOIN
persons
ON
persons.person_id = json_to_parse.person_id;
but this does not
SELECT
*
FROM
json_to_parse,
json_to_record(json_to_parse.block) AS my_json(size TEXT, love TEXT)
INNER JOIN
persons
ON
persons.person_id = json_to_parse.person_id;
and I get the error "invalid reference to FROM-clause entry for table "json_to_parse""
Why does this second query error? [The docs](https://www.postgresql.org/docs/current/queries-table-expressions.html#QUERIES-LATERAL) make it quite clear that LATERAL
is optional for table-valued functions
> 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.
[dbfiddle](https://dbfiddle.uk/An86bWru)
Asked by J. Mini
(1235 rep)
Dec 1, 2024, 03:30 PM
Last activity: Dec 2, 2024, 12:00 AM
Last activity: Dec 2, 2024, 12:00 AM