Looping through databases in postgresql function

Question

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?

jgrocha · Accepted Answer · 2020-07-12 22:55:43Z

4

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

answered Jul 12, 2020 at 22:55

jgrocha

3,1121 gold badge30 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Richard Huxton · Accepted Answer · 2012-08-22 15:32:22Z

2

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.

answered Aug 22, 2012 at 15:32

Richard Huxton

23.6k5 gold badges43 silver badges53 bronze badges

1 Comment

jonas Over a year ago

You're correct -- what I'm trying to do is fundamentally wrong. Looks like I'll have to port this to bash scripts. Thanks!

Kuberchaun · Accepted Answer · 2012-08-22 15:08:58Z

1

Isn't db name in pg_database of type name?

Try DECLARE db_name NAME;

answered Aug 22, 2012 at 15:08

Kuberchaun

30.5k8 gold badges54 silver badges59 bronze badges

1 Comment

jonas Over a year ago

You're absolutely correct. The datname column is of type NAME.

Collectives™ on Stack Overflow

Looping through databases in postgresql function

3 Answers 3

Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related