Postgres - Run same query on all databases (same schemas)

Question

hoping this is a pretty straightforward question.

I have a straightforward SELECT query (with a few sub-queries built in). I have over 40 DBs and I need to run this query for all DBs (all have same schema) and return the result in a big table.

I'm imagining a loop sequence (like with javascript's i=0; i < 40; i++) with a defined variable that will automatically stop once it's run all the DBs.

(I am working in Navicat, tho that probably doesn't matter)

Thank you!

Answer 1

In case someone needs a more involved example on how to do cross-database queries, here's an example that cleans up the databasechangeloglock table on every database that has it:

CREATE EXTENSION IF NOT EXISTS dblink;

DO 
$$
DECLARE database_name TEXT;
DECLARE conn_template TEXT;
DECLARE conn_string TEXT;
DECLARE table_exists Boolean;
BEGIN
    conn_template = 'user=myuser password=mypass dbname=';

    FOR database_name IN
        SELECT datname FROM pg_database
        WHERE datistemplate = false
    LOOP
        conn_string = conn_template || database_name;

        table_exists = (select table_exists_ from dblink(conn_string, '(select Count(*) > 0 from information_schema.tables where table_name = ''databasechangeloglock'')') as (table_exists_ Boolean));
        IF table_exists THEN
            perform dblink_exec(conn_string, 'delete from databasechangeloglock');
        END IF;     
    END LOOP;

END
$$