From: David Johnston <polobo@yahoo.com>
To: pgsql-sql@postgresql.org
Subject: Re: json and aggregate
Date: Mon, 4 Nov 2013 12:38:16 -0800 (PST)
Message-ID: <1383597496358-5776905.post@n5.nabble.com> (raw)
In-Reply-To: <1383596556949-5776903.post@n5.nabble.com>
References: <1383596556949-5776903.post@n5.nabble.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>
Diway wrote
> Why is the following query not working ?
> select sum((json_array_elements(data)->>'lines')::integer) as test from
> test2 where id = 2;
> ERROR: set-valued function called in context that cannot accept a set
>
> ('data' is obviously a json datatype)
>
> On the other side, this one is OK but I don't like it ;-)
> select sum(value) from (select
> (json_array_elements(data)->>'lines')::integer as value from test2 where
> id = 2) x;
How technical an answer do you want?
Short answer is that GROUP BY/aggregates cannot process a
set-returning-function (SRF) in the select-list. You have move the SRF into
the associated FROM clause and let the individual rows feed from there into
the GROUP BY/aggregates.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: polobo@yahoo.com
Subject: Re: json and aggregate
In-Reply-To: <1383597496358-5776905.post@n5.nabble.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox