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.
So, I found a workaround...
First I made a proxy function on which the first proxy function will run :
CREATE OR REPLACE FUNCTION p_count_toto( in_id_toto INTEGER ) RETURNS INTEGER AS $func$ BEGIN RETURN (SELECT sum(tmp_nb_toto) out_nb_toto FROM count_comm(in_id_alias)); END; $func$ LANGUAGE plpgsql;
And I modified my first PL/Proxy function which looks like this now :
CREATE OR REPLACE FUNCTION count_toto( in_id_toto INTEGER, OUT tmp_nb_toto INTEGER ) RETURNS SETOF INTEGER AS $$ CLUSTER 'main'; RUN ON ALL; $$ LANGUAGE plproxy;
I think that's not the most elegant way to do it, because at the application level, devs will have to change the name of the procedure they'll call, but it does the work.