Is it intentional that LATERAL elements in a USING clause of a DELETE
statement can't reference the table declared in the FROM clause?
Here's a somewhat contrived example. Suppose I have a table with one
jsonb column:
create table int_arrays (int_array jsonb);
insert into int_arrays values ('[1]'), ('[1, 2]'), ('[3, 4, 5]'),
('[1, 1, 1]');
If I want to delete every row whose array contains a value greater
than one, I would expect the following query to work:
delete from int_arrays using jsonb_array_each(int_array) _ (val)
where val::integer > 1;
But that fails with:
ERROR: invalid reference to FROM-clause entry for table "int_arrays"
LINE 1: delete from int_arrays using jsonb_array_each(int_array) _ (...
^
HINT: There is an entry for table "int_arrays", but it cannot be
referenced from this part of the query.
So, ok, fine, the FROM and USING clauses are different scopes or
something. Except that doesn't quite explain the situation, because
you can't reuse the FROM table name in the USING clause:
# delete from int_arrays using int_arrays;
ERROR: table name "int_arrays" specified more than once
Can someone shed some light on the situation here? Is there a reason
that LATERAL elements in the USING clause must be prevented from
accessing the FROM table or is the restriction just emergent behavior?
Nikhil
