DDL language recognition to allow idempotent type creation

[dbhoot]

DDL language recognition to allow idempotent type creation

[kyleconroy]

There are a few ways to workaround this issue. Understand and interpreting DO functions is going to be pretty far off. Instead of having sqlc rely on your migrations directory, you could output a dump of your SQL database after your migrations have been run.

The other option is to create another, sqlc-specific, migrations directory where you have the CREATE TYPE outside the DO function.

I understand both of these options aren't ideal, but I'd like to get you unblocked.

[dbhoot]

These are great alternatives. Thank you, I was about to unblock myself.

If understanding the DO block is out of band, would a structured comment be more achievable ?

[andrewmbenton]

If understanding the DO block is out of band, would a structured comment be more achievable ?

We have a few "structured comment" proposals in the works. Are you suggesting something like:

-- @sqlc-ddl "CREATE TYPE status AS ENUM ('pending', 'cancelled', 'completed', 'failed', 'reversed');"
DO $$
  BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'status') THEN
      CREATE TYPE status AS ENUM ('pending', 'cancelled', 'completed', 'failed', 'reversed');
    END IF;
  END
$$;

[dbhoot]

Yes, something along those lines.