From: | Merlin Moncure <mmoncure(at)gmail(dot)com> |
---|---|
To: | Rémi Cura <remi(dot)cura(at)gmail(dot)com> |
Cc: | PostgreSQL General <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: best practice for || set of rows --> function --> set of rows |
Date: | 2013-09-17 13:11:38 |
Message-ID: | CAHyXU0ytRL=ZauGGaoPe2WHKeuxVLGozj2sDcgQw40Krf4XxRw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Albe Laurenz | 2013-09-17 13:22:01 | Re: need a hand with my backup strategy please... |
Previous Message | Echlin, Jamie (KFIA 611) | 2013-09-17 12:54:06 | Re: need a hand with my backup strategy please... |