On 2022-08-15 12:20:44 -0700, Bryn Llewellyn wrote:
>
> pedz@easesoftware.com wrote:
>
> Currently I’m doing this with two queries such as:
>
> SELECT COUNT(*) FROM table WHERE …. expression …
> SELECT * FROM table WHERE …. expression …
>
> But this requires two queries. Is there a way to do the same thing
> with just one quer?
>
>
> david.g.johnston@gmail.com wrote:
>
> Use count as a window function.
>
>
> hjp-pgsql@hjp.at wrote:
>
> I don't think there can be [a way to do the same thing with just one
> query].
That's not quite what I meant. I meant "I don't think there can be what
you want with just one query",
> How about this:
>
> create table t(k serial primary key, v int not null);
> insert into t(v) values (7), (19), (42), (57), (100), (200), (300);
>
> create function f()
> returns table(z text)
> language plpgsql
> stable
> as $body$
> declare
> r int not null := 0;
> results constant int[] :=
> (
> select array_agg(v order by v) from t where v < 100
> );
> begin
> z := 'Count(*): '||cardinality(results); return next;
I may be missing something but I don't see how this solves the problem.
The OP wants some kind of progress indicator. To be useful, such an
indicator should be approximately linear in time. I.e. if your query
returns 10000 rows in 5 minutes (yes, that's slow, but you don't need a
progress bar for fast queries), it should display "0/10000" after 0
seconds, "33/10000" after 1 second, "2000/10000" after 1 minute, etc.
That ideal is of course unrealistic, it's quite ok if it displays
"0/unknown" fpr a few seconds and speeds up and slows down during
execution. But if it displays "0/unknown" for 4 minutes and 55 seconds
and then counts up to 10000 during the last 5 seconds, the progress
indicator is useless.
You are stuffing the whole result into an array and THEN counting the
number of elements. So when you get to the count all of the work (except
sending the result to the client) is already done, so there is little
point in displaying a progress indicator.
hp
--
_ | Peter J. Holzer | Story must make more sense than reality.
|_|_) | |
| | | hjp@hjp.at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"