Re: Converting row elements into a arrays?

[Merlin Moncure <mmoncure@xxxxxxxxx>]

On Thu, Mar 2, 2023 at 3:47 PM Ron <ronljohnsonjr@xxxxxxxxx> 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