Aggregate functions with FROM clause and ROW_COUNT diagnostics

Поиск
Список
Период
Сортировка
От Alexey Dokuchaev
Тема Aggregate functions with FROM clause and ROW_COUNT diagnostics
Дата
Msg-id 20180521125441.GA85087@regency.nsu.ru
обсуждение исходный текст
Ответы Re: Aggregate functions with FROM clause and ROW_COUNT diagnostics  ("David G. Johnston" <david.g.johnston@gmail.com>)
Re: Aggregate functions with FROM clause and ROW_COUNT diagnostics  (Adrian Klaver <adrian.klaver@aklaver.com>)
Список 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


В списке pgsql-general по дате отправления:

Предыдущее
От: greigwise
Дата:
Сообщение: Errors with physical replication
Следующее
От: "David G. Johnston"
Дата:
Сообщение: Re: [GENERAL] Postgre compatible version with RHEL 7.5