On 09.02.23 10:42, Vik Fearing wrote:
v4 attached. I am putting this back to Needs Review in the commitfest
app, but these changes were editorial so it is probably RfC like Peter
had set it. I will let you be the judge of that.
I have committed this.
I made a few small last-minute tweaks:
I could have used such an aggregate in the past, so +1.
This is maybe getting into nit-picking, but perhaps it should be
documented as returning an "arbitrary" value instead of a
"non-deterministic" one? Technically the value is deterministic:
there's a concrete algorithm specifying how it's selec
any_value() is not
worth the dozen or so lines of code it would take to write it. I'm
assuming it would always be a MONOTONICFUNC_BOTH function. It seems
unlikely that someone would have a subquery with a WHERE clause in the
upper-level query referencing the any_value() aggregate. Thought I'
o, I'm currently
thinking that a SupportRequestWFuncMonotonic for any_value() is not
worth the dozen or so lines of code it would take to write it. I'm
assuming it would always be a MONOTONICFUNC_BOTH function. It seems
unlikely that someone would have a subquery with a WHERE clause in the
uppe
On 18.01.23 18:01, Vik Fearing wrote:
On 1/18/23 16:06, Peter Eisentraut wrote:
On 05.12.22 21:18, Vik Fearing wrote:
On 12/5/22 15:57, Vik Fearing wrote:
The SQL:2023 Standard defines a new aggregate named ANY_VALUE. It
returns an implementation-dependent (i.e. non-deterministic) value
from
ATCH] Implement ANY_VALUE aggregate
SQL:2023 defines an ANY_VALUE aggregate whose purpose is to emit an
implementation-dependent (i.e. non-deterministic) value from the
aggregated rows.
---
doc/src/sgml/func.sgml | 14 ++
src/backend/catalog/sql_features.txt | 1
On 1/18/23 16:55, Tom Lane wrote:
Peter Eisentraut writes:
On 05.12.22 21:18, Vik Fearing wrote:
On 12/5/22 15:57, Vik Fearing wrote:
The SQL:2023 Standard defines a new aggregate named ANY_VALUE. It
returns an implementation-dependent (i.e. non-deterministic) value
from the rows in its grou
Peter Eisentraut writes:
> On 05.12.22 21:18, Vik Fearing wrote:
>> On 12/5/22 15:57, Vik Fearing wrote:
>>> The SQL:2023 Standard defines a new aggregate named ANY_VALUE. It
>>> returns an implementation-dependent (i.e. non-deterministic) value
>>> from the rows in its group.
> Since the tran
On 05.12.22 21:18, Vik Fearing wrote:
On 12/5/22 15:57, Vik Fearing wrote:
The SQL:2023 Standard defines a new aggregate named ANY_VALUE. It
returns an implementation-dependent (i.e. non-deterministic) value
from the rows in its group.
PFA an implementation of this aggregate.
Here is v2 of
st_value aggregate (which is by definition
non-standard) whether you like it or not.
I am de jure creating an any_value aggregate (which is by definition
standard) whether you like it or not.
Yes, both statements seem true. At least until we decide to start ignoring
a user's explicit
ers...
We should update our documentation - the source of SQL Standard knowledge
for mere mortals.
https://www.postgresql.org/docs/current/sql-expressions.html#SYNTAX-AGGREGATES
"Note: The ability to specify both DISTINCT and ORDER BY in an aggregate
function is a PostgreSQL extension."
t or not.
I am de jure creating an any_value aggregate (which is by definition
standard) whether you like it or not.
I'm just saying to be upfront
and honest about it - our users do want such a capability so maybe accept
that there is a first time for everything. Not that picking an
advantag
On Wed, Dec 7, 2022 at 1:58 AM Pantelis Theodosiou
wrote:
> On Tue, Dec 6, 2022 at 4:57 AM David G. Johnston
> wrote:
> ...
> >
> >
> > I'm referring to the query:
> >
> > select any_value(v order by v) from (values (2),(1),(3)) as vals (v);
> > // produces 1, per the documented implementation-d
On Tue, Dec 6, 2022 at 4:57 AM David G. Johnston
wrote:
...
>
>
> I'm referring to the query:
>
> select any_value(v order by v) from (values (2),(1),(3)) as vals (v);
> // produces 1, per the documented implementation-defined behavior.
>
> Someone writing:
>
> select any_value(v) from (values (2)
On Mon, Dec 5, 2022 at 10:40 PM Vik Fearing wrote:
> On 12/6/22 05:57, David G. Johnston wrote:
> > On Mon, Dec 5, 2022 at 9:48 PM Vik Fearing
> wrote:
> >
> >> I can imagine an optimization that would remove an ORDER BY clause
> >> because it isn't needed for any other aggregate.
> >
> >
> > I'
On 12/6/22 05:57, David G. Johnston wrote:
On Mon, Dec 5, 2022 at 9:48 PM Vik Fearing wrote:
I can imagine an optimization that would remove an ORDER BY clause
because it isn't needed for any other aggregate.
I'm referring to the query:
select any_value(v order by v) from (values (2),(1),(
On Mon, Dec 5, 2022 at 9:48 PM Vik Fearing wrote:
> On 12/6/22 05:22, David G. Johnston wrote:
> > On Mon, Dec 5, 2022 at 8:46 PM Vik Fearing
> wrote:
> >
> >> On 12/5/22 18:56, David G. Johnston wrote:
> >>> Also, maybe we should have any_value do something like compute a 50/50
> >>> chance tha
On 12/6/22 05:22, David G. Johnston wrote:
On Mon, Dec 5, 2022 at 8:46 PM Vik Fearing wrote:
On 12/5/22 18:56, David G. Johnston wrote:
Also, maybe we should have any_value do something like compute a 50/50
chance that any new value seen replaces the existing chosen value,
instead
of simply
On Mon, Dec 5, 2022 at 8:46 PM Vik Fearing wrote:
> On 12/5/22 18:56, David G. Johnston wrote:
> > Also, maybe we should have any_value do something like compute a 50/50
> > chance that any new value seen replaces the existing chosen value,
> instead
> > of simply returning the first value all th
On Mon, 5 Dec 2022 at 22:52, Vik Fearing wrote:
> On 12/5/22 20:31, Corey Huinker wrote:
> >
> > Adding to the pile of wanted aggregates: in the past I've lobbied for
> > only_value() which is like first_value() but it raises an error on
> > encountering a second value.
>
> I have had use for thi
On 12/5/22 20:31, Corey Huinker wrote:
Adding to the pile of wanted aggregates: in the past I've lobbied for
only_value() which is like first_value() but it raises an error on
encountering a second value.
I have had use for this in the past, but I can't remember why. What is
your use case fo
On 12/5/22 18:56, David G. Johnston wrote:
Also, maybe we should have any_value do something like compute a 50/50
chance that any new value seen replaces the existing chosen value, instead
of simply returning the first value all the time. Maybe even prohibit the
first value from being chosen so
update sql_features.txt.
--
Vik Fearing
From a9bb61aab9788ae25fdcd28f7dcfb54a263665cc Mon Sep 17 00:00:00 2001
From: Peter Eisentraut
Date: Sat, 9 Apr 2022 00:07:38 +0200
Subject: [PATCH] Implement ANY_VALUE aggregate
SQL:2023 defines an ANY_VALUE aggregate whose purpose is to emit an
implementation
On Mon, Dec 5, 2022 at 2:31 PM Corey Huinker wrote:
> Adding to the pile of wanted aggregates: in the past I've lobbied for
> only_value() which is like first_value() but it raises an error on
> encountering a second value.
Yeah, that's another that I have hand-rolled in the past.
--
Robert H
On Mon, Dec 5, 2022 at 12:57 PM David G. Johnston <
david.g.johns...@gmail.com> wrote:
> On Mon, Dec 5, 2022 at 7:57 AM Vik Fearing
> wrote:
>
>> The SQL:2023 Standard defines a new aggregate named ANY_VALUE. It
>> returns an implementation-dependent (i.e. non-deterministic) value from
>> the ro
On Mon, Dec 5, 2022 at 1:04 PM Tom Lane wrote:
> "David G. Johnston" writes:
> > Can we please add "first_value" and "last_value" if we are going to add
> > "some_random_value" to our library of aggregates?
>
> First and last according to what ordering? We have those in the
> window-aggregate ca
"David G. Johnston" writes:
> Can we please add "first_value" and "last_value" if we are going to add
> "some_random_value" to our library of aggregates?
First and last according to what ordering? We have those in the
window-aggregate case, and I don't think we want to encourage people
to believ
On Mon, Dec 5, 2022 at 7:57 AM Vik Fearing wrote:
> The SQL:2023 Standard defines a new aggregate named ANY_VALUE. It
> returns an implementation-dependent (i.e. non-deterministic) value from
> the rows in its group.
>
> PFA an implementation of this aggregate.
>
>
Can we please add "first_value
going through a LATERAL subquery.
--
Vik FearingFrom 7465fac12fc636ff26088ae31de2937f7c3a459f Mon Sep 17 00:00:00 2001
From: Peter Eisentraut
Date: Sat, 9 Apr 2022 00:07:38 +0200
Subject: [PATCH] Implement ANY_VALUE aggregate
SQL:2023 defines an ANY_VALUE aggregate whose purpose is to emit an
29 matches
Mail list logo
