Re: hstore each() function - returned order??

On Fri, Mar 12, 2021 at 3:18 AM Brent Wood <Brent(dot)Wood(at)niwa(dot)co(dot)nz> wrote:

> *From:* David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com>
> *Sent:* Friday, March 12, 2021 21:19
> *To:* Brent Wood <Brent(dot)Wood(at)niwa(dot)co(dot)nz>
> *Cc:* pgsql-general(at)lists(dot)postgresql(dot)org <
> pgsql-general(at)lists(dot)postgresql(dot)org>
> *Subject:* Re: hstore each() function - returned order??
>
>
> Thanks David,
>
> Is this what you mean?
>
> select measurement_key,
> value
> from 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.