Обсуждение: Implementing IF NOT EXISTS clause for all CREATE TYPE variants

I would like to propose adding IF NOT EXISTS support to all CREATE TYPE 
variants
for consistency with other CREATE commands (CREATE TABLE, CREATE INDEX, 
etc.). I
occasionally find myself writing something similar to this:

     DO $$ BEGIN
         CREATE TYPE t AS ( ... );
         EXCEPTION
             WHEN duplicate_object THEN null;
         END $$;

but I would prefer to be able to do:

     CREATE TYPE IF NOT EXISTS t AS ( ... );

I don't see an immediate reason why this has not been implemented, and 
also did
not find anything on the mailing list archives. I would like to try my 
hand at
implementing this. As this would be my first contribution, I would like to
discuss what changes would have to be made.

I expect to add the following:
- IF NOT EXISTS clause support to all five forms of CREATE TYPE:
     1. Composite types: CREATE TYPE IF NOT EXISTS name AS (...)
     2. Enum types: CREATE TYPE IF NOT EXISTS name AS ENUM (...)
     3. Range types: CREATE TYPE IF NOT EXISTS name AS RANGE (...)
     4. Base types: CREATE TYPE IF NOT EXISTS name (INPUT = ..., OUTPUT 
= ...)
     5. Shell types: CREATE TYPE IF NOT EXISTS name
- A NOTICE when IF NOT EXISTS is specified and a type with the same name 
already exists
- Tests for the new syntax and functionality
- Documentation for the IF NOT EXISTS clause
- Tab completion (though this is missing for all other IF NOT EXISTS 
clauses, and the
   tab completion handling looks quite complex, so maybe not)

For that, I expect to make the following changes:
- Grammar changes in src/backend/parser/gram.y for CompositeTypeStmt, 
CreateEnumStmt, and
   CreateRangeStmt (DefineStmt is already correct), together with the 
corresponding changes
   to src/include/nodes/parsenodes.h.
- Implement the if_not_exists handling in src/backend/commands/typecmds.c:
     if if_not_exists is true: verify extension membership, issue a 
NOTICE and return
     InvalidObjectAddress
     else: use the existing error handling
- update DefineType and CompositeType calls to pass if_not_exists in
   src/backend/tcop/utility.c
- regression tests in src/test/regress/sql/create_type.sql
- documentation in doc/src/sgml/ref/create_type.sgml
- tab completion in src/bin/psql/tab-complete.in.c for CREATE TYPE IF 
NOT EXISTS statements
   only

Does this sound reasonable?

Kind regards,
Bram Hagens

Bram Hagens <bram@bramh.me> writes:
> I would like to propose adding IF NOT EXISTS support to all CREATE TYPE 
> variants
> for consistency with other CREATE commands (CREATE TABLE, CREATE INDEX, 
> etc.).

There has been a *ton* of discussion about this over the years.
If you didn't find anything in the archives, you didn't look hard.

The short answer about why this isn't as useful as it might seem
is that after CINE, your application is entitled to make exactly
zero assumptions about the properties of the object, other than
that it exists.  CREATE OR REPLACE functionality is a lot more
reliable to depend on, since if it succeeds you know exactly
how the object is defined.  (However, then you have the issue
of whether the server can cope with possibly propagating changes
in the object definition.)

            regards, tom lane