need of a lateral join with record set returning function?

From: Raphael Bauduin <rblists(at)gmail(dot)com>
To: PostgreSQL General <pgsql-general(at)postgresql(dot)org>
Subject: need of a lateral join with record set returning function?
Date: 2014-02-03 16:10:31
Message-ID: CAONrwUFreWYB2WdgmNa80_+SA8tDcKSnwJxJo3FybZdgAp6OTA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi,

I'm trying to understand what happens here:

I have atype product defined:
=# \d product
Composite type "public.product"
Column | Type | Modifiers
-----------------+------------------+-----------
price_advantage | double precision |
type | integer |
gender | text |
status | integer |
brand | integer |
price | double precision |
id | integer |
algorithm | text |

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,)

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?

Thanks

Raph

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.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message David Johnston 2014-02-03 17:43:27 Re: need of a lateral join with record set returning function?
Previous Message amihay gonen 2014-02-03 14:34:59 Q: How to use indexer api smartly