How to iterate in a loop to generate databases in Postgres for testing?

Question

I want to simulate a distributed database, so I am generating 10 isolated databases, 3 replicas each, for a total of 30 database instances. I am doing this all locally for the moment, and am running something like this:

drop database if exists ddsjs_test_1_replica_1;
drop database if exists ddsjs_test_2_replica_1;
drop database if exists ddsjs_test_3_replica_1;
drop database if exists ddsjs_test_4_replica_1;
drop database if exists ddsjs_test_5_replica_1;
drop database if exists ddsjs_test_6_replica_1;
drop database if exists ddsjs_test_7_replica_1;
drop database if exists ddsjs_test_8_replica_1;
drop database if exists ddsjs_test_9_replica_1;
drop database if exists ddsjs_test_10_replica_1;

create database ddsjs_test_1_replica_1;
create database ddsjs_test_2_replica_1;
create database ddsjs_test_3_replica_1;
create database ddsjs_test_4_replica_1;
create database ddsjs_test_5_replica_1;
create database ddsjs_test_6_replica_1;
create database ddsjs_test_7_replica_1;
create database ddsjs_test_8_replica_1;
create database ddsjs_test_9_replica_1;
create database ddsjs_test_10_replica_1;

...

How do I make this dynamic in SQL for Postgres? Like looping and incrementing i?

Not sure how you would do this in SQL as you can't create a database within a transaction which you would likely need as part of the incrementing number. You could wrap this in a scripting language script. — Gurmokh
– Gurmokh, Commented Feb 13, 2022 at 20:43

Ramin Faracov · Accepted Answer · 2022-02-24 05:13:05Z

1

Sample Function:

CREATE OR REPLACE FUNCTION create_databases_dynamic()
RETURNS bool 
LANGUAGE plpgsql
AS $function$
declare
    v_index integer;
    sql_drop text;
    sql_create text;
begin
    
    sql_drop   = 'drop database if exists ddsjs_test_%s_replica_1'; 
    sql_create = 'create database ddsjs_test_%s_replica_1';
    
    for v_index in 1..10 
    loop
        EXECUTE format(sql_drop, v_index);
        EXECUTE format(sql_create, v_index);
    end loop;

    return true; 

END;
$function$;

answered Feb 24, 2022 at 5:13

Ramin Faracov

3,3331 gold badge6 silver badges10 bronze badges

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

Collectives™ on Stack Overflow

How to iterate in a loop to generate databases in Postgres for testing?

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