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

Hi,

I'm seeing somewhat confusing results here with 9.6.8, and cannot find
the answer in the docs or google.

I'm returning JSON array (or any array, it does not make a difference)
from my plpgsql function like this:

    OUT retcode int,
    OUT result json)
    . . .
    result := json_agg(_) FROM (
      SELECT foo, bar, baz ...
      FROM t1, t2, t3 WHERE ...) AS _;        -- this works fine

    GET DIAGNOSTICS retcode = ROW_COUNT;    -- always returns 1

I'd expected `retcode' to contain the number of SELECT'ed rows, but it
is something else (always 1).  Apparently, aggregation functions like
json_agg()/array_agg() mangle the ROW_COUNT from the inner SELECT (the
one I'm interested in).

Is this expected and correct behavior?  Is it possible to obtain the
first ROW_COUNT (after SELECT) without performing it twice?  Thanks,

./danfe