Re: Can I get the number of results plus the results with a single query?
| От | Bryn Llewellyn |
|---|---|
| Тема | Re: Can I get the number of results plus the results with a single query? |
| Дата | |
| Msg-id | C6C274DF-62A3-4E54-AC83-F0FB3D7017EC@yugabyte.com обсуждение исходный текст |
| Ответ на | Re: Can I get the number of results plus the results with a single query? ("Peter J. Holzer" <hjp-pgsql@hjp.at>) |
| Ответы |
Re: Can I get the number of results plus the results with a single query?
|
| Список | pgsql-general |
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?
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();
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
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?
В списке pgsql-general по дате отправления: