Re: best practice for || set of rows --> function --> set of rows

On Tue, Sep 17, 2013 at 2:06 AM, Rémi Cura <remi(dot)cura(at)gmail(dot)com> wrote:
> The only kind of function taking set of record as input I know of is
> aggregate function, but it returns only one row and the output of union can
> take multiple row.

This may or may not help (I suggest posting a more complete example of
what you are unable to do):

If your output list of records is (quite) small, you may be able to
get away with using an aggregate function.

CREATE OR REPLACE append3 (anyarray, anyelement) RETURNS anyarray AS
$$
SELECT CASE
WHEN $1 IS NULL THEN ARRAY[$2]
WHEN array_upper($1,1) >= 3 THEN $1
ELSE $1 || $2
END;
$$ LANGUAGE SQL;

CREATE AGGREGATE agg_append3(anyelement) (SFUNC = append3, STYPE = anyarray);

CREATE TABLE foo(a int, b text);

INSERT INTO foo SELECT s, s::text FROM generate_series(1,10) s;

WITH data AS (SELECT unnest(agg_append3(f)) AS d FROM foo f) SELECT
(d).* FROM data;
a | b
---+---
1 | 1
2 | 2
3 | 3

User defined aggregates can be defined over window function partitions:

SELECT a, agg_append3(f) OVER (PARTITION BY a % 2 ORDER BY a DESC) AS
d FROM foo f;
a | d
----+-----------------------------
10 | {"(10,10)"}
8 | {"(10,10)","(8,8)"}
6 | {"(10,10)","(8,8)","(6,6)"}
4 | {"(10,10)","(8,8)","(6,6)"}
2 | {"(10,10)","(8,8)","(6,6)"}
9 | {"(9,9)"}
7 | {"(9,9)","(7,7)"}
5 | {"(9,9)","(7,7)","(5,5)"}
3 | {"(9,9)","(7,7)","(5,5)"}
1 | {"(9,9)","(7,7)","(5,5)"}

merlin