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

[Merlin Moncure <mmoncure@xxxxxxxxx>]

On Tue, Sep 17, 2013 at 2:06 AM, Rémi Cura <remi.cura@xxxxxxxxx> 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


-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general