hstore each() function - returned order??

On Thursday, March 11, 2021, Brent Wood <Brent.Wood@niwa.co.nz> wrote:

Hi,

I'm using the following in an SQL :

select (EACH(value)).key as measurement_key,
(EACH(value)).value as value
from t_reading_hstore;

Just move the each call to the from clause as a lateral join and treat the
result as a two-column table.

David J.

From: David G. Johnston <david.g.johnston@gmail.com>
Sent: Friday, March 12, 2021 21:19
To: Brent Wood <Brent.Wood@niwa.co.nz>
Cc: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.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.

Cheers

Brent Wood

On Thursday, March 11, 2021, Brent Wood <Brent.Wood@niwa.co.nz<mailto:Brent.Wood@niwa.co.nz>> wrote:
Hi,

I'm using the following in an SQL :

select (EACH(value)).key as measurement_key,
(EACH(value)).value as value
from t_reading_hstore;

Just move the each call to the from clause as a lateral join and treat the result as a two-column table.

David J.

[https://www.niwa.co.nz/static/niwa-2018-horizontal-180.png][https://niwa.co.nz/sites/niwa.co.nz/files/ETNZ_Official-supplier-logo-75h.png]&lt;https://www.niwa.co.nz&gt;
Brent Wood
Principal Technician - GIS and Spatial Data Management
Programme Leader - Environmental Information Delivery
+64-4-386-0529
National Institute of Water & Atmospheric Research Ltd (NIWA)
301 Evans Bay Parade Hataitai Wellington New Zealand
Connect with NIWA: niwa.co.nz<https://www.niwa.co.nz&gt; Facebook<https://www.facebook.com/nzniwa&gt; LinkedIn<https://www.linkedin.com/company/niwa&gt; Twitter<https://twitter.com/niwa_nz&gt; Instagram<https://www.instagram.com/niwa_science&gt;
To ensure compliance with legal requirements and to maintain cyber security standards, NIWA's IT systems are subject to ongoing monitoring, activity logging and auditing. This monitoring and auditing service may be provided by third parties. Such third parties can access information transmitted to, processed by and stored on NIWA's IT systems

Hi

On 2021-03-12 04:46, Brent Wood wrote:

Hi,

I'm using the following in an SQL :

select (EACH(value)).key as measurement_key,
(EACH(value)).value as value from t_reading_hstore;

I'm assuming this will give two columns containing the key/value pairs
in the hstore record.

The docs suggest the order of the keys/values returned is
undetermined. That is a bit ambiguous to me.

The order is not a problem in this case: as long as the keys and
values are returned in the SAME order, what that order is I don't
care.

Just that the key is always returned in the same row as its value.

It does seem to work, at least in my test cases, but I need to know if
that is just luck, or if it is a safe assumption to make.

Can anyone confirm this is a robust assumption for me?

You can order the result by key to have a specific order that remains
between calls.

select (EACH(value)).key as measurement_key,
(EACH(value)).value as value from t_reading_hstore
order by (EACH(value)).key;

Bye
Charles

Thanks

Brent Wood

Principal Technician, Fisheries
NIWA
DDI: +64 (4) 3860529

[1]

Brent Wood
Principal Technician - GIS and Spatial Data Management
Programme Leader - Environmental Information Delivery
+64-4-386-0529
National Institute of Water & Atmospheric Research Ltd (NIWA)
301 Evans Bay Parade Hataitai Wellington New Zealand
Connect with NIWA: niwa.co.nz [1] Facebook [2] LinkedIn [3] Twitter
[4] Instagram [5]

To ensure compliance with legal requirements and to maintain cyber
security standards, NIWA's IT systems are subject to ongoing
monitoring, activity logging and auditing. This monitoring and
auditing service may be provided by third parties. Such third parties
can access information transmitted to, processed by and stored on
NIWA's IT systems

Links:
------
[1] https://www.niwa.co.nz
[2] https://www.facebook.com/nzniwa
[3] https://www.linkedin.com/company/niwa
[4] https://twitter.com/niwa_nz
[5] https://www.instagram.com/niwa_science

--
Charles Clavadetscher
Swiss PostgreSQL Users Group
Treasurer
Spitzackerstrasse 9
CH - 8057 Zürich

http://www.swisspug.org

+---------------------------+
| ____ ______ ___ |
| / )/ \/ \ |
| ( / __ _\ ) |
| \ (/ o) ( o) ) |
| \_ (_ ) \ ) _/ |
| \ /\_/ \)/ |
| \/ <//| |\\> |
| _| | |
| \|_/ |
| |
| Swiss PostgreSQL |
| Users Group |
| |
+---------------------------+

On Fri, Mar 12, 2021 at 3:18 AM Brent Wood <Brent.Wood@niwa.co.nz> wrote:

*From:* David G. Johnston <david.g.johnston@gmail.com>
*Sent:* Friday, March 12, 2021 21:19
*To:* Brent Wood <Brent.Wood@niwa.co.nz>
*Cc:* pgsql-general@lists.postgresql.org <
pgsql-general@lists.postgresql.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.

"David G. Johnston" <david.g.johnston@gmail.com> writes:

On Fri, Mar 12, 2021 at 3:18 AM Brent Wood <Brent.Wood@niwa.co.nz> wrote:

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;

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.

Yeah. To my mind, the main point here is to run each() just once per
t_reading_hstore row, not twice. So something like

SELECT e.key, e.value FROM t_reading_hstore AS t, each(t.value) AS e;

(Writing LATERAL is optional.)

regards, tom lane

Ahh, light dawns!!!
Thanks guys, much appreciated!!!  (as always)

Brent

On Saturday, March 13, 2021, 3:52:33 AM GMT+13, Tom Lane <tgl@sss.pgh.pa.us> wrote:

"David G. Johnston" <david.g.johnston@gmail.com> writes:

On Fri, Mar 12, 2021 at 3:18 AM Brent Wood <Brent.Wood@niwa.co.nz> wrote:

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;

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.

Yeah.  To my mind, the main point here is to run each() just once per
t_reading_hstore row, not twice.  So something like

SELECT e.key, e.value FROM t_reading_hstore AS t, each(t.value) AS e;

(Writing LATERAL is optional.)

            regards, tom lane