Re: Converting row elements into a arrays?

[Merlin Moncure]

On Thu, Mar 2, 2023 at 3:47 PM Ron <ronljohnso...@gmail.com> wrote

> I'm used to the bog standard COUNT, AVG, MIN, MAX, SUM. It didn't occur to
> me that there would be others...
>

wait until you find out you can write your own:

CREATE OR REPLACE FUNCTION agg_leftagg(l TEXT, r anyelement) returns text as
$$
BEGIN
  RETURN CASE
    WHEN l IS NOT NULL THEN  format('%s-%s', l, r)
    ELSE r::TEXT
  END;
END;
$$ LANGUAGE PLPGSQL;

CREATE AGGREGATE leftagg(anyelement) (SFUNC=agg_leftagg, STYPE=TEXT);

CREATE TEMP TABLE s AS SELECT generate_series(1,10) s;

SELECT leftagg(s) FROM s GROUP BY random() > .5;

postgres=# SELECT leftagg(s) FROM s GROUP BY random() > .5;
  leftagg
────────────
 2-3-5-6-10
 1-4-7-8-9
(2 rows)

this is why I fell in love with postgres 20 years ago, and never looked back

merlion