Re: Aggregate functions with FROM clause and ROW_COUNT diagnostics

Поиск
Список
Период
Сортировка
От Alexey Dokuchaev
Тема Re: Aggregate functions with FROM clause and ROW_COUNT diagnostics
Дата
Msg-id 20180521162831.GA97361@regency.nsu.ru
обсуждение исходный текст
Ответ на Re: Aggregate functions with FROM clause and ROW_COUNT diagnostics  (Tom Lane <tgl@sss.pgh.pa.us>)
Ответы Re: Aggregate functions with FROM clause and ROW_COUNT diagnostics  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-general
On Mon, May 21, 2018 at 10:45:27AM -0400, Tom Lane wrote:
> OP could do something like
> 
>     declare r record;
>     ...
>     select json_agg(_) as j, count(*) as c INTO r FROM (
>       SELECT foo, bar, baz ...
>       FROM t1, t2, t3 WHERE ...) AS _;
> 
> This would be slightly more expensive than doing only the one aggregate,
> but it should beat anything involving a temp table.

Thanks, I've arrived at the same solution (using local RECORD) eventually.
It works as intended, but I still need to assign OUT parameters by hand:

    retcode := tmp.c;
    result := tmp.j;

I'd love to get rid of OUT parameters and return RECORD directly (it also
would make the code more "functional" in style), but then I'd be getting
this annoying ``a column definition list is required for functions
returning "record"'' error which I don't know how to avoid.  Using OUT's
simplifies things for callers (they don't have to annotate the types by
hand) while still preserving type safety.

On Mon, May 21, 2018 at 07:30:44AM -0700, David G. Johnston wrote:
> > ...
> > 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).

I see, thanks; I've neglected the fact that there's still another implicit
SELECT being involved.

./danfe


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

Предыдущее
От: Stephen Frost
Дата:
Сообщение: Re: Will Altering and Modifying tables during backup result in acorrupted server after the restore?
Следующее
От: Christoph Moench-Tegeder
Дата:
Сообщение: Re: Will Altering and Modifying tables during backup result in acorrupted server after the restore?