On Mon, Dec 7, 2020 at 9:54 PM Tom Lane <tgl@xxxxxxxxxxxxx> wrote:
Ken Tanzer <ken.tanzer@xxxxxxxxx> writes:
> There's one last piece of this query I'm clearly not getting though. Where
> it says:
> from foo as f, jsonb_to_recordset(js) as t(key2 text)
> what is actually going on there? I keep reading this as a table foo (f)
> cross-joined to a table created by jsonb_to_recordset (t). But that
> doesn't seem right, because rows from t are only joining with matching rows
> from f, rather than all of them. Is there some unspoken implicit logic
> going on here, or something else entirely that is going over my head?
There's an implicit LATERAL there:
... from foo as f, lateral jsonb_to_recordset(js) as t(key2 text)
ie, for each row of foo, extract the foo.js column and evaluate
jsonb_to_recordset(js) --- which, in this case, produces multiple
rows that are joined to the original foo row. This is again a
SQL-ism. I don't particularly care for their choice to allow
LATERAL to be implicit for function-call-like FROM items,
because it seems pretty confusing; but the spec is the spec.
That's (finally!) making sense to me.
[ thinks for a bit... ] Again, I'm too lazy to go digging in
the spec's dense verbiage at this hour, but I'm vaguely recalling
that they may only require this behavior for the one case of
the function being UNNEST(). I think it was our choice to allow
it to work like that for any set-returning function.
The SELECT page David pointed me towards has a little section that seems to confirm your recollection:
Function Calls in FROM
PostgreSQL allows a function call to be written directly as a member of the FROM list. In the SQL standard it would be necessary to wrap such a function call in a sub-SELECT; that is, the syntax FROM func(...) alias is approximately equivalent to FROM LATERAL (SELECT func(...)) alias. Note that LATERAL is considered to be implicit; this is because the standard requires LATERAL semantics for an UNNEST() item in FROM. PostgreSQL treats UNNEST() the same as other set-returning functions.
Cheers,
Ken
AGENCY Software
A Free Software data system
By and for non-profits
(253) 245-3801
learn more about AGENCY or
follow the discussion.
