Re: Can I get the number of results plus the results with a single query?

>>> pedz(at)easesoftware(dot)com <mailto:pedz(at)easesoftware(dot)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(dot)g(dot)johnston(at)gmail(dot)com wrote:
>>
>> Use count as a window function.
>
> hjp-pgsql(at)hjp(dot)at wrote:
>
> I don't think there can be [a way to do the same thing 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;

foreach r in array results loop
z := r::int; return next;
end loop;
end;
$body$;

select f();

It suns without error and gets this result:

Count(*): 4
7
19
42
57

Is this what you want, Perry? B.t.w., your second "count(*)" might give a misleading answer unless you use "repeatable read" or "serializable"—and it's generally a good plan to avoid those isolation levels unless your use case forces no other choice.

I've never heard anybody say that this "bulk collect" approach (using Oracle Database's vocabulary) harms performance. Does anybody think that it might?