Creating databases in for loop, injecting iterator of loop to database name

Question

I would like to create four databases in for-loop. However, I got an error. Could you help me to resolve this problem ?

DO $$
    BEGIN
        FOR counter IN 1..2 LOOP
            CREATE DATABASE 'database_name_%', counter;
        END LOOP;
END; $$

ERROR:  syntax error at or near "'Counter: %'"
LINE 4:             CREATE DATABASE 'Counter: %', counter;

khoroshevj · Accepted Answer · 2018-03-04 11:34:36Z

1

From documentation for the CREATE DATABASE command:

CREATE DATABASE cannot be executed inside a transaction block.

And since:

PostgreSQL actually treats every SQL statement as being executed within a transaction. If you do not issue a BEGIN command, then each individual statement has an implicit BEGIN and (if successful) COMMIT wrapped around it. A group of statements surrounded by BEGIN and COMMIT is sometimes called a transaction block

You can't create database in functions. So even if you can handle current problem, you won't be able to execute this function.

answered Mar 4, 2018 at 11:34

khoroshevj

1,13710 silver badges16 bronze badges

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

Collectives™ on Stack Overflow

Creating databases in for loop, injecting iterator of loop to database name

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related