On Fri, Mar 12, 2021 at 3:18 AM Brent Wood <Brent.Wood@xxxxxxxxxx> wrote:
From: David G. Johnston <david.g.johnston@xxxxxxxxx>
Sent: Friday, March 12, 2021 21:19
To: Brent Wood <Brent.Wood@xxxxxxxxxx>
Cc: pgsql-general@xxxxxxxxxxxxxxxxxxxx <pgsql-general@xxxxxxxxxxxxxxxxxxxx>
Subject: Re: hstore each() function - returned order??
Thanks David,
Is this what you mean?
select measurement_key,valuefrom t_reading_hstore,lateral ((EACH(value)).key as measurement_key,
lateral ((EACH(value)).value as value;
I'll try it with \timing & explain to look at performance, but I don't see how this provides any greater assurance that the keys & values are provided in the same order, and so the correct value is in the same row as its key?
This still seems to depend on just how the EACH() function returns the values rather than any use of lateral joins, and was the question I was asking.
The definition of "each" shows that it returns a record type. That whole type can be considered a table and referenced in the select list. Thus you only need to invoke each once, then in the select list of the query reference its two fields (which are already named, though you can give them an alias in the from clause if you want "each(value) as comp (measurement_key, measurement value)" and "select comp.measurement_key, comp.measurement_value".
David J.
