pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Alban Hertroys <haramrae@gmail.com>
To: Thorsten Glaser <tg@evolvis.org>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-general@lists.postgresql.org
Subject: Re: DISTINCT *and* ORDER BY in aggregate functions on expressions(!)y
Date: Tue, 28 Feb 2023 09:10:54 +0100
Message-ID: <D9BE942C-58B4-48D0-9DA1-2B7514BA4881@gmail.com> (raw)
In-Reply-To: <9fc8e12b-f943-c633-a37-254915f819a0@evolvis.org>
References: <23d08470-aaa8-afca-dfd7-37261487a1f8@evolvis.org>
	<CAKFQuwZ1f0zmVsFpYeNL8c=waGSCJB5K4AmkqzdXXHXB3XX3Lw@mail.gmail.com>
	<c02191c1-64a8-3350-6853-784bf5ce57@evolvis.org>
	<559416.1677547978@sss.pgh.pa.us>
	<9fc8e12b-f943-c633-a37-254915f819a0@evolvis.org>



> On 28 Feb 2023, at 3:54, Thorsten Glaser <tg@evolvis.org> wrote:

(…)

>> Well, that may be what you want, but it's not what you wrote in
>> the query.  Follow David's advice and do
> […]
>> I'm pretty sure that this will only incur one evaluation of the
>> common subexpression, so even though it's tedious to type it's not
>> inefficient.
> 
> Thanks. But I fear it’s not as simple as you wrote. More like:
> 
> jsonb_build_object('opening_times',
> 	jsonb_agg(DISTINCT jsonb_build_object(
> 		'weekday', cot.weekday,
> 		'from_hour', cot.from_hour,
> 		'to_hour', cot.to_hour)
> 	ORDER BY
> 	    jsonb_build_object(
> 		'weekday', cot.weekday,
> 		'from_hour', cot.from_hour,
> 		'to_hour', cot.to_hour)->>'weekday',
> 	    jsonb_build_object(
> 		'weekday', cot.weekday,
> 		'from_hour', cot.from_hour,
> 		'to_hour', cot.to_hour)->>'from_hour',
> 	    jsonb_build_object(
> 		'weekday', cot.weekday,
> 		'from_hour', cot.from_hour,
> 		'to_hour', cot.to_hour)->>'to_hour')
> )
> 
> Isn’t that more like it?


Perhaps you can use a lateral cross join to get the result of jsonb_build_object as a jsonb value to pass around?


Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.






view thread (14+ messages)  latest in thread

Message-ID: <D9BE942C-58B4-48D0-9DA1-2B7514BA4881@gmail.com>
Permalink:  ../D9BE942C-58B4-48D0-9DA1-2B7514BA4881@gmail.com/
Also on:    postgresql.org/message-id/D9BE942C-58B4-48D0-9DA1-2B7514BA4881@gmail.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-general@postgresql.org
  Cc: haramrae@gmail.com, tg@evolvis.org, tgl@sss.pgh.pa.us
  Subject: Re: DISTINCT *and* ORDER BY in aggregate functions on expressions(!)y
  In-Reply-To: <D9BE942C-58B4-48D0-9DA1-2B7514BA4881@gmail.com>
  References: <23d08470-aaa8-afca-dfd7-37261487a1f8@evolvis.org> <CAKFQuwZ1f0zmVsFpYeNL8c=waGSCJB5K4AmkqzdXXHXB3XX3Lw@mail.gmail.com> <c02191c1-64a8-3350-6853-784bf5ce57@evolvis.org> <559416.1677547978@sss.pgh.pa.us> <9fc8e12b-f943-c633-a37-254915f819a0@evolvis.org> <D9BE942C-58B4-48D0-9DA1-2B7514BA4881@gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox