I am creating a bunch of schemas in a loop. I have something like this:
FOR i in 0 .. num_schemas LOOP
schema_name := 'test' || i;
CREATE SCHEMA testschema;
CREATE TABLE testschema.testtable (
test_id UUID PRIMARY KEY,
test_address VARCHAR
);
END LOOP;
But, this is not working; it tries to create a schema with the literal name 'testschema', instead of test0, test1... test'n'. So, how can I use a variable in this sort of query?
You need dynamic SQL for that. But instead of prefixing each table, I would change the current schema after creating it. That way you don't need to prefix every table you create:
FOR i in 0 .. num_schemas LOOP
schema_name := 'test' || i;
execute 'CREATE SCHEMA '||schema_name;
execute 'set search_path = '||schema_name;
CREATE TABLE testtable ( -- this will be created in the just created schema
test_id UUID PRIMARY KEY,
test_address VARCHAR
);
END LOOP;
You should make use of execute command.
instead of CREATE SCHEMA testschema you can specify execute 'CREATE SCHEMA '|| schema_name;
