Postgresql – SUM() the result of a function

aggregatefunctionspartitioningpostgresqlsharding

I use the Pl/Proxy extension in order to shard a table. It works like a charm, except for this :

I have a function which runs on 4 shards. It looks like this :

CREATE OR REPLACE
FUNCTION count_comm(
    id_toto INTEGER,
    OUT out_nb_toto INTEGER
) RETURNS SETOF INTEGER AS $$
        CLUSTER 'main'; RUN ON ALL;
$$ LANGUAGE plproxy;

It returns me a SETOF INTEGERS like this :

p_commentaire=> SELECT count_toto(42);
 count_commentaire_from_id_toto 
---------------------------------
                         2172022

So basically, each of the result is the COUNT() of each shard, it works great as I said.
The thing is, I wanted to know if there's a way to SUM() all the results to get a total instead of having the 4 results individually …

Edit : Just to add something, PlProxy needs a 'SETOF' return when you run the query on all shards.

Best Answer

You could just use the SQL statement:

SELECT sum(out_nb_comm) AS sum_out_nb_comm
FROM   count_comm($in_id_alias);

Or, if you want to wrap it in a function, a simple SQL function does the job:

CREATE OR REPLACE FUNCTION sum_count_comm(in_id_alias int)
   RETURNS int AS
$func$
SELECT sum(out_nb_comm)::int
FROM   count_comm($1));
$func$ LANGUAGE sql;
Related Question