Aggregate functions with FROM clause and ROW_COUNT diagnostics

From: Alexey Dokuchaev <danfe(at)nsu(dot)ru>
To: pgsql-general(at)postgresql(dot)org
Subject: Aggregate functions with FROM clause and ROW_COUNT diagnostics
Date: 2018-05-21 12:54:41
Message-ID: 20180521125441.GA85087@regency.nsu.ru
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message David G. Johnston 2018-05-21 13:24:01 Re: [GENERAL] Postgre compatible version with RHEL 7.5
Previous Message greigwise 2018-05-21 12:18:57 Errors with physical replication