On Mon, Dec 7, 2020 at 9:54 PM Tom Lane <t...@sss.pgh.pa.us> wrote: > Ken Tanzer <ken.tan...@gmail.com> 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 *http://agency-software.org/ <http://agency-software.org/>* *https://demo.agency-software.org/client <https://demo.agency-software.org/client>* ken.tan...@agency-software.org (253) 245-3801 Subscribe to the mailing list <agency-general-requ...@lists.sourceforge.net?body=subscribe> to learn more about AGENCY or follow the discussion.
