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
