database/setup.sh fails on fresh PostgreSQL: CREATE DATABASE inside DO $$ block

[org-event]

Summary

database/setup.sh fails on a fresh PostgreSQL instance when running database/seeds/001-install.sql.

The marcus role is created, but the application database is not. The next steps in setup.sh then fail because the database does not exist.

Steps to reproduce

git clone https://github.com/metarhia/Example.git
cd Example

docker run --rm --name pgtest -e POSTGRES_PASSWORD=test -d postgres:17-alpine
until docker exec pgtest pg_isready -U postgres; do sleep 1; done

docker cp database/seeds/001-install.sql pgtest:/tmp/001-install.sql
docker exec pgtest psql -U postgres -v ON_ERROR_STOP=1 -f /tmp/001-install.sql

Actual result

DO
psql:/tmp/001-install.sql:15: ERROR:  CREATE DATABASE cannot be executed from a function
CONTEXT:  SQL statement "CREATE DATABASE application OWNER marcus"
PL/pgSQL function inline_code_block line 4 at SQL statement

Cause

PostgreSQL does not allow CREATE DATABASE inside a PL/pgSQL DO $$ block. Reproduced on PostgreSQL 12–17.

Context

Related to #297: seeds were added for docker-compose, where POSTGRES_DB=application created the database before init scripts ran, so the failing block in 001-install.sql was masked. After #305 (docker removed), database/setup.sh is the main manual path, but it fails on a clean database.

Suggested fix

Replace the second DO $$ block in database/seeds/001-install.sql with:

DO $$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'marcus') THEN
    CREATE ROLE marcus LOGIN PASSWORD 'marcus';
  END IF;
END
$$;

SELECT 'CREATE DATABASE application OWNER marcus'
WHERE NOT EXISTS (SELECT FROM pg_database WHERE datname = 'application')\gexec

(\gexec is a psql meta-command; setup.sh already uses psql -f.)

Happy to open a PR if this approach looks good.