Re: need of a lateral join with record set returning function?

Raphael Bauduin wrote
> Hi,
>
> I'm trying to understand what happens here:
>
> I have atype product defined:
>
> [...]
>
> which I'm trying to use in this query calling json_populate_recordset
>
> =# select q.* from (select json_populate_recordset(null::product,
> event->'products') from events where timestamp>'2014-02-02' and
> type='gallery' limit 1) q;
> json_populate_recordset
> -----------------------------
> (68,121,F,3,493,17,88753,)

SELECT (q.json_populate_recordset).* FROM ( .... LIMIT 1) q; will possibly
give you what you desire. the presence of limit will avoid having the
function evaluated multiple-times.

> This query illustrates what I want to achieve:
>
> =# select f.* from
> json_populate_recordset(null::product,'[{"id":80723,"type":41,"brand":41,"price":65.0,"status":3,"price_advantage":1.0,"gender":"M",
> "algorithm":"v1"}]'::json) f;
> price_advantage | type | gender | status | brand | price | id |
> algorithm
> -----------------+------+--------+--------+-------+-------+-------+-----------
> 1 | 41 | M | 3 | 41 | 65 | 80723 | v1
>
> I see the difference in the query ( the second working directly on the
> return value of the function), but in the first example, isn"t the inner
> returning a set, from which the outer query can do a select *?
> There is a difference with the second query which I've not identified.
> Anyone caring to enlighten me?

When the function is in the FROM clause it is treated like a table and so
each output value gets its own column on the "table" that is created.

When the function is in the "SELECT-list" it is treated like a composite
type and thus only occupies a single output column. You can manually
de-reference the composite type into a "table" structure using "*" -ON THE
COLUMN- as a separate encapsulating action.

> PS: to get it working, I have to write the query as this:
>
> =# select q.* from (select * from events where timestamp>'2014-02-02' and
> type='gallery') q1 CROSS JOIN LATERAL
> json_populate_recordset(null::product, event->'products') q limit 1;
> price_advantage | type | gender | status | brand | price | id |
> algorithm
> -----------------+------+--------+--------+-------+-------+-------+-----------
> 68 | 121 | F | 3 | 493 | 17 | 88753 |
>
> What I'm interested is an explanation of why this is needed.

Correct, because now the function is in the FROM-clause and not the
SELECT-list. This is pretty much the reason LATERAL exists - to keep the
function in the FROM-clause while still allowing it to reference columns
like it can when it is part of the SELECT-list.

Without LATERAL you have to put the function in the SELECT-list and make
sure it executes only a single time [i.e., (function_call(...)).* does NOT
work performantly for sundry technical reasons] after which you can, in an
outer-query-layer, expand the composite type into component parts. The main
way to enforce this behavior is to use CTE/WITH:

WITH func_cte AS (
SELECT func_call(...) AS f_result FROM ....
)
SELECT (func_cte.f_result).* FROM func_cte;

Note the syntax for expanding the column includes the () surrounding the
"table.column" style identifier.

David J.

--
View this message in context: http://postgresql.1045698.n5.nabble.com/need-of-a-lateral-join-with-record-set-returning-function-tp5790353p5790366.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.