CODEWITHSUNDEEP
sqlpostgresql
user3663882
user3663882

Reputation: 7357

Is it possible to union results in postgresql?

I need to union tables aggregated from different queries and return the whole result set from a stored procedure. Here is what I mean:

CREATE OR REPLACE FUNCTION invalid_pa() RETURNS TABLE (p_id int) AS $$
DECLARE
    pl int[] := ARRAY(SELECT id FROM p.pl);
BEGIN
    FOR i IN 1 .. array_upper(platforms, 1)
    LOOP
          --The query is SELECT id FROM dblink(connection_str(i), 'Some query')
          --I need to union all the query above
          --and return the unioned table
    END LOOP;
END $$
LANGUAGE plpgsql;

How can I union different result sets in such a way?

connection_str(i) here is a stored procedure returning the actual connection string

Upvotes: 3

Views: 1727

Answers (1)

Patrick
Patrick

Reputation: 32199

There are some things amiss with your posted code, but ignoring those the solution would be somewhat like this:

CREATE OR REPLACE FUNCTION invalid_pa() RETURNS TABLE (p_id int) AS $$
DECLARE
  pl int[] := ARRAY(SELECT id FROM p.pl); -- ???
BEGIN
  FOR i IN 1 .. array_upper(platforms, 1) -- platforms???
  LOOP
    RETURN QUERY SELECT id FROM dblink(connection_str(i), 'Some query');
  END LOOP;
  RETURN;
END $$ LANGUAGE plpgsql;

RETURN QUERY adds zero or more rows to the result set and the final RETURN returns the entire result set to the caller. Effectively, the results from all the dblink calls are UNIONed together.

Upvotes: 4

Related Questions