From: | "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> |
---|---|
To: | Alexey Dokuchaev <danfe(at)nsu(dot)ru> |
Cc: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Aggregate functions with FROM clause and ROW_COUNT diagnostics |
Date: | 2018-05-21 14:30:44 |
Message-ID: | CAKFQuwaQ_jGJF5OZEvfz=J99BS5i6BeBZO+35RQnK-foBo1EPQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Mon, May 21, 2018 at 5:54 AM, Alexey Dokuchaev <danfe(at)nsu(dot)ru> wrote:
> 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?
Yes, the query returned only one row, with a single json column. You
wrote the equivalent of:
SELECT json_agg(...) FROM ... INTO result;
And you are getting the count of the top-most select (which is implied in
the syntax that you used).
> 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.
David J.
From | Date | Subject | |
---|---|---|---|
Next Message | Adrian Klaver | 2018-05-21 14:35:06 | Re: Aggregate functions with FROM clause and ROW_COUNT diagnostics |
Previous Message | Thiagarajan Lakshminarayanan | 2018-05-21 14:23:28 | Installing PostgreSQL as non-root by Extracting the binaries from RPMs |