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

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

In response to

Browse pgsql-general by date

  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...