Apply migrations for a list of schema

[chr1shung]

I have a use case to create multiple DB schemas and for each schema they all have the same tables(migrations):

CREATE SCHEMA IF NOT EXISTS schema_1;
CREATE SCHEMA IF NOT EXISTS schema_2;
...

-- for each schema they all have the same tables
CREATE TABLE IF NOT EXISTS table_1;
CREATE TABLE IF NOT EXISTS table_2;
...

I'm wondering if there's an elegant way to do this using goose ?
If not what's the possible solution to achieve this ? I could only come up with using shell script and variable substitution but not sure if that's possible....

thanks for the help .

[chr1shung]

Just figured out I could use SQL statement to do so:

-- +goose Up
-- +goose StatementBegin
DO $$
DECLARE
    schema_name text;
BEGIN
    -- Loop through and create schemas from 1 to 3
    FOR i IN 1..3 LOOP
        schema_name := 'schema_' || i;

        -- Create schema if it doesn't exist
        EXECUTE format('CREATE SCHEMA IF NOT EXISTS %I', schema_name);

        EXECUTE format('
            CREATE TABLE IF NOT EXISTS %I.table_1 (
                id TEXT PRIMARY KEY,
                name TEXT NOT NULL
            )', schema_name);

        -- Add more tables as needed following the same pattern
    END LOOP;
END;
$$;
-- +goose StatementEnd

-- +goose Down
-- +goose StatementBegin
DO $$
DECLARE
    schema_name text;
BEGIN
    -- Drop schemas and all contained objects
    FOR i IN 1..3 LOOP
        schema_name := 'schema_' || i;
        EXECUTE format('DROP SCHEMA IF EXISTS %I CASCADE', schema_name);
    END LOOP;
END;
$$;
-- +goose StatementEnd