Re: Aggregate functions with FROM clause and ROW_COUNT diagnostics

On 05/21/2018 05:54 AM, Alexey Dokuchaev wrote:
> 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,

Off the top of my head:

  SELECT count(*) as ct, foo, bar, baz ...

retcode = result ->'ct'

> 
> ./danfe
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com