PostgreSQL: function to query across multiple databases

Question

I have several databases on the same PostgreSQL server with the exact same tables with the same columns in it. I want to write a function that a user could use to query across all these databases at once, something like:

SELECT * FROM all_databases();

For the moment, I just found how to query another database:

-- 1. Get database names
SELECT datname
FROM pg_database
WHERE name LIKE '%someString%';

-- 2. Get data from different databases with postgres_fdw (same host or remote host)
-- 2.1. Install the module
CREATE EXTENSION postgres_fdw;
-- 2.2. Create a server connection
CREATE SERVER foreign_db  
FOREIGN DATA WRAPPER postgres_fdw  
OPTIONS (host 'localhost', dbname 'foreignDbName', port '5432');
-- 2.3. Create user mapping for the foreign server
CREATE USER MAPPING FOR CURRENT_USER  
SERVER foreign_db  
OPTIONS (user 'postgres', password 'password');  
-- 2.4. Import the foreign schema
IMPORT FOREIGN SCHEMA public  
FROM SERVER foreign_db INTO public; 

So, what I want to do is to execute something like what is written in 2 for every result returned by 1. It looks like I will have to use some dynamic SQL, but I am a little bit lost...

Answer 1

Firstly, if the tables in both dbs have the same name, you can't import it in the same schema, you have to import it in another schema or manually with another foreign table name.(see CREATE FOREIGN TABLE)

Secondly, you can do your query with a simple SELECT over your foreign tables. Eg.

CREATE SCHEMA ft_db2; -- foreign tables db2 schema

IMPORT FOREIGN SCHEMA public  
FROM SERVER foreign_db INTO ft_db2;

CREATE OR REPLACE FUNCTION all_databases()
  RETURNS SETOF public.test AS
$$
SELECT * FROM public.test
UNION ALL
SELECT * FROM ft_db2.test;
$$
  LANGUAGE sql;

SELECT * FROM all_databases();

Answer 2

maybe something like SELECT * FROM *;