Sample Header Ad - 728x90

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