| From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
|---|---|
| To: | "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> |
| Cc: | Alexey Dokuchaev <danfe(at)nsu(dot)ru>, pgsql-general <pgsql-general(at)postgresql(dot)org> |
| Subject: | Re: Aggregate functions with FROM clause and ROW_COUNT diagnostics |
| Date: | 2018-05-21 14:45:27 |
| Message-ID: | 30939.1526913927@sss.pgh.pa.us |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-general |
"David G. Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> writes:
>> Is it possible to obtain the
>> first ROW_COUNT (after SELECT) without performing it twice?
> Not directly, no. You should execute the inner query to a temporary table
> than perform your counting and json_agg from that.
OP could do something like
declare r record;
...
select json_agg(_) as j, count(*) as c INTO r FROM (
SELECT foo, bar, baz ...
FROM t1, t2, t3 WHERE ...) AS _;
This would be slightly more expensive than doing only the one aggregate,
but it should beat anything involving a temp table.
regards, tom lane
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Yashwanth Govinda Setty | 2018-05-21 15:03:18 | Will Altering and Modifying tables during backup result in a corrupted server after the restore? |
| Previous Message | Adrian Klaver | 2018-05-21 14:35:06 | Re: Aggregate functions with FROM clause and ROW_COUNT diagnostics |