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

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 |
| |
+---------------------------+