Re: Aggregate functions with FROM clause and ROW_COUNT diagnostics - Mailing list pgsql-general

"David G. Johnston" <david.g.johnston@gmail.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