CODEWITHSUNDEEP
functionpostgresql
jonas
jonas

Reputation: 143

Looping through databases in postgresql function

I'd like to loop through a list of postgresql databases and run some queries on them from within a postgresql function. Here's a code example...

    CREATE OR REPLACE FUNCTION my_function() 
    RETURNS VOID AS  
    $$
    DECLARE
        db VARCHAR(50); -- this declaration is where the confusion is
    BEGIN
        FOR db IN 
            SELECT datname FROM pg_catalog.pg_database WHERE datname ~ '^mydbname_'
        LOOP
            -- this is just an example
            SELECT * FROM db;
        END LOOP;
    END; 
    $$
    LANGUAGE 'plpgsql';

I'm aware that I can use postgresql's EXECUTE to evaluate the queries as a string (e.g., EXECUTE 'SELECT * FROM ' || db || ';';), but my queries are rather long and complex.

Is there a way to do this in postgresql? Is there a "database" declaration type?

Upvotes: 2

Views: 6419

Answers (3)

jgrocha
jgrocha

Reputation: 3052

I'm not able to do it with a Postgresql function. If it helps, here is a very simple bash script to iterate over all databases:

#!/bin/bash
 
all="SELECT datname FROM pg_database WHERE datistemplate = false and datname != 'postgres'"
psql -h host -U user postgres --no-align -t -c "${all}" | while read -a bd ; do
    echo "Processing ${bd}..."
    psql -h host -U user "${bd}" -c "SELECT current_database()"
    # psql -h host -U user "${bd}" -f fix.sql 
done

Upvotes: 4

Richard Huxton
Richard Huxton

Reputation: 22943

You can't use a variable as an object-name (database, table, column) in a query directly. You'll have to use EXECUTE.

This isn't going to work anyway because you can't do cross-database queries. Either do this from the client or look at using dblink. There is an implementation of SQL/MED (Foreign Data Wrappers) but oddly I don't think there is a PostgreSQL wrapper yet.

Upvotes: 2

Kuberchaun
Kuberchaun

Reputation: 30342

Isn't db name in pg_database of type name?

Try DECLARE db_name NAME;

Upvotes: 1

Related Questions