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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general