Re: json and aggregate

David Johnston wrote
> 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.
>
> From the documentation:
>
> json_array_elements(json) [returns] SETOF json
>
> the "SETOF" is the indicator of a SRF. The reference to "set-valued
> function" is another term for this concept.
>
> So, yes, the version you do not like as well is required. First you break
> apart the json THEN you can aggregate.
>
> David J.

Ok, thanks! I'm pretty new to postgres "advanced" features an didn't even
know what a SRF was :)

Another question if you don't mind, I've written the following function to
convert many field to a json field and then group them based on another id.
I don't really it because I have to query each line...

---
CREATE OR REPLACE FUNCTION fn_tojson()
RETURNS TABLE(id integer, data json)
AS
$$
DECLARE
current_id integer;
BEGIN
FOR current_id IN(
SELECT nah.id FROM item_header nah
) LOOP
id := current_id;

data := (SELECT json_agg(x) FROM (SELECT message_id, dt_created,
lines, size, current_part, status, fk_item_cat_id FROM item na WHERE
na.fk_item_header_id = $1) x);

RETURN NEXT;
END LOOP;
END;
$$
LANGUAGE 'plpgsql' VOLATILE;
---

I could use something like this (not tested!)
--
select fk_item_header_id as id, json_agg(row(message_id, dt_created, lines,
size, current_part, status, fk_item_cat_id)) as data from item group by
fk_item_header_id;
--
but my json object keys would be translated to f1, f2, ... and I really need
to keep the original ones.

Thanks again!

--
View this message in context: http://postgresql.1045698.n5.nabble.com/json-and-aggregate-tp5776903p5776914.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.