diff --git a/doc/src/sgml/ref/create_aggregate.sgml b/doc/src/sgml/ref/create_aggregate.sgml index 17819dd..63779c1 100644 --- a/doc/src/sgml/ref/create_aggregate.sgml +++ b/doc/src/sgml/ref/create_aggregate.sgml @@ -21,7 +21,7 @@ PostgreSQL documentation -CREATE AGGREGATE name ( [ argmode ] [ arg_name ] arg_data_type [ , ... ] ) ( +CREATE AGGREGATE [ IF NOT EXISTS ] name ( [ argmode ] [ arg_name ] arg_data_type [ , ... ] ) ( SFUNC = sfunc, STYPE = state_data_type [ , SSPACE = state_data_size ] @@ -32,7 +32,7 @@ CREATE AGGREGATE name ( [ or the old syntax -CREATE AGGREGATE name ( +CREATE AGGREGATE [ IF NOT EXISTS ] name ( BASETYPE = base_type, SFUNC = sfunc, STYPE = state_data_type @@ -179,6 +179,16 @@ SELECT col FROM tab ORDER BY col USING sortop LIMIT 1; + IF NOT EXISTS + + + Do nothing (except issuing a notice) if an aggregate function with + the same argument types already exists. + + + + + name diff --git a/doc/src/sgml/ref/create_cast.sgml b/doc/src/sgml/ref/create_cast.sgml index 29ea298..1c4c1df 100644 --- a/doc/src/sgml/ref/create_cast.sgml +++ b/doc/src/sgml/ref/create_cast.sgml @@ -18,15 +18,15 @@ -CREATE CAST (source_type AS target_type) +CREATE CAST [ IF NOT EXISTS ] (source_type AS target_type) WITH FUNCTION function_name (argument_type [, ...]) [ AS ASSIGNMENT | AS IMPLICIT ] -CREATE CAST (source_type AS target_type) +CREATE CAST [ IF NOT EXISTS ] (source_type AS target_type) WITHOUT FUNCTION [ AS ASSIGNMENT | AS IMPLICIT ] -CREATE CAST (source_type AS target_type) +CREATE CAST [ IF NOT EXISTS ] (source_type AS target_type) WITH INOUT [ AS ASSIGNMENT | AS IMPLICIT ] @@ -171,6 +171,16 @@ SELECT CAST ( 2 AS numeric ) + 4.0; Parameters + + IF NOT EXISTS + + + Do nothing (except issuing a notice) if a cast with the same + from and to type already exists. + + + + source_type diff --git a/doc/src/sgml/ref/create_collation.sgml b/doc/src/sgml/ref/create_collation.sgml index c853576..f93d87e 100644 --- a/doc/src/sgml/ref/create_collation.sgml +++ b/doc/src/sgml/ref/create_collation.sgml @@ -18,12 +18,12 @@ -CREATE COLLATION name ( +CREATE COLLATION [ IF NOT EXISTS ] name ( [ LOCALE = locale, ] [ LC_COLLATE = lc_collate, ] [ LC_CTYPE = lc_ctype ] ) -CREATE COLLATION name FROM existing_collation +CREATE COLLATION [ IF NOT EXISTS ] name FROM existing_collation @@ -47,6 +47,16 @@ CREATE COLLATION name FROM existing_coll Parameters + + IF NOT EXISTS + + + Do nothing (except issuing a notice) if an collation with the same + name already exists. + + + + name diff --git a/doc/src/sgml/ref/create_domain.sgml b/doc/src/sgml/ref/create_domain.sgml index 49db069..23b7611 100644 --- a/doc/src/sgml/ref/create_domain.sgml +++ b/doc/src/sgml/ref/create_domain.sgml @@ -21,7 +21,7 @@ PostgreSQL documentation -CREATE DOMAIN name [ AS ] data_type +CREATE DOMAIN [ IF NOT EXISTS ] name [ AS ] data_type [ COLLATE collation ] [ DEFAULT expression ] [ constraint [ ... ] ] @@ -71,6 +71,16 @@ CREATE DOMAIN name [ AS ] + IF NOT EXISTS + + + Do nothing (except issuing a notice) if a domain with the same name + already exists. + + + + + name diff --git a/doc/src/sgml/ref/create_event_trigger.sgml b/doc/src/sgml/ref/create_event_trigger.sgml index ed66322..cc5969a 100644 --- a/doc/src/sgml/ref/create_event_trigger.sgml +++ b/doc/src/sgml/ref/create_event_trigger.sgml @@ -21,7 +21,7 @@ PostgreSQL documentation -CREATE EVENT TRIGGER name +CREATE EVENT TRIGGER [ IF NOT EXISTS ] name ON event [ WHEN filter_variable IN (filter_value [, ... ]) [ AND ... ] ] EXECUTE PROCEDURE function_name() @@ -46,6 +46,16 @@ CREATE EVENT TRIGGER name + IF NOT EXISTS + + + Do nothing (except issuing a notice) if a event trigger the + same name already exists. + + + + + name diff --git a/doc/src/sgml/ref/create_operator.sgml b/doc/src/sgml/ref/create_operator.sgml index dd33f06..80a6bf6 100644 --- a/doc/src/sgml/ref/create_operator.sgml +++ b/doc/src/sgml/ref/create_operator.sgml @@ -21,7 +21,7 @@ PostgreSQL documentation -CREATE OPERATOR name ( +CREATE OPERATOR [ IF NOT EXISTS ] name ( PROCEDURE = function_name [, LEFTARG = left_type ] [, RIGHTARG = right_type ] [, COMMUTATOR = com_op ] [, NEGATOR = neg_op ] @@ -117,6 +117,16 @@ CREATE OPERATOR name ( + IF NOT EXISTS + + + Do nothing (except issuing a notice) if an operator with the same + name already exists. + + + + + name diff --git a/doc/src/sgml/ref/create_role.sgml b/doc/src/sgml/ref/create_role.sgml index 7ec4d0a..f9e3696 100644 --- a/doc/src/sgml/ref/create_role.sgml +++ b/doc/src/sgml/ref/create_role.sgml @@ -21,7 +21,7 @@ PostgreSQL documentation -CREATE ROLE name [ [ WITH ] option [ ... ] ] +CREATE ROLE [ IF NOT EXISTS ] name [ [ WITH ] option [ ... ] ] where option can be: @@ -69,6 +69,17 @@ CREATE ROLE name [ [ WITH ] Parameters + + + IF NOT EXISTS + + + Do nothing (except issuing a notice) if a role with the same name + already exists. + + + + name diff --git a/doc/src/sgml/ref/create_sequence.sgml b/doc/src/sgml/ref/create_sequence.sgml index 38d160d..982ffe3 100644 --- a/doc/src/sgml/ref/create_sequence.sgml +++ b/doc/src/sgml/ref/create_sequence.sgml @@ -21,7 +21,7 @@ PostgreSQL documentation -CREATE [ TEMPORARY | TEMP ] SEQUENCE name [ INCREMENT [ BY ] increment ] +CREATE [ TEMPORARY | TEMP ] [ IF NOT EXISTS ] SEQUENCE name [ INCREMENT [ BY ] increment ] [ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUE maxvalue | NO MAXVALUE ] [ START [ WITH ] start ] [ CACHE cache ] [ [ NO ] CYCLE ] [ OWNED BY { table_name.column_name | NONE } ] @@ -90,6 +90,16 @@ SELECT * FROM name; + IF NOT EXISTS + + + Do nothing (except issuing a notice) if a sequence with the same name + already exists. + + + + + name @@ -343,8 +353,8 @@ END; - The OWNED BY clause is a PostgreSQL - extension. + The OWNED BY and IF NOT EXISTS clause + is a PostgreSQL extension. diff --git a/doc/src/sgml/ref/create_tsconfig.sgml b/doc/src/sgml/ref/create_tsconfig.sgml index c34d1c0..2cc7c1f 100644 --- a/doc/src/sgml/ref/create_tsconfig.sgml +++ b/doc/src/sgml/ref/create_tsconfig.sgml @@ -21,7 +21,7 @@ PostgreSQL documentation -CREATE TEXT SEARCH CONFIGURATION name ( +CREATE TEXT SEARCH CONFIGURATION [ IF NOT EXISTS ] name ( PARSER = parser_name | COPY = source_config ) @@ -66,6 +66,16 @@ CREATE TEXT SEARCH CONFIGURATION name + IF NOT EXISTS + + + Do nothing (except issuing a notice) if a text search configuration + with the same name already exists. + + + + + name diff --git a/doc/src/sgml/ref/create_tsdictionary.sgml b/doc/src/sgml/ref/create_tsdictionary.sgml index 2673bc5..4ffd408 100644 --- a/doc/src/sgml/ref/create_tsdictionary.sgml +++ b/doc/src/sgml/ref/create_tsdictionary.sgml @@ -21,7 +21,7 @@ PostgreSQL documentation -CREATE TEXT SEARCH DICTIONARY name ( +CREATE TEXT SEARCH DICTIONARY [ IF NOT EXISTS ] name ( TEMPLATE = template [, option = value [, ... ]] ) @@ -59,6 +59,16 @@ CREATE TEXT SEARCH DICTIONARY name + IF NOT EXISTS + + + Do nothing (except issuing a notice) if a text search dictionary + with the same name already exists. + + + + + name diff --git a/doc/src/sgml/ref/create_tsparser.sgml b/doc/src/sgml/ref/create_tsparser.sgml index 7643f08..1631af4 100644 --- a/doc/src/sgml/ref/create_tsparser.sgml +++ b/doc/src/sgml/ref/create_tsparser.sgml @@ -21,7 +21,7 @@ PostgreSQL documentation -CREATE TEXT SEARCH PARSER name ( +CREATE TEXT SEARCH PARSER [ IF NOT EXISTS ] name ( START = start_function , GETTOKEN = gettoken_function , END = end_function , @@ -64,6 +64,16 @@ CREATE TEXT SEARCH PARSER name ( + IF NOT EXISTS + + + Do nothing (except issuing a notice) if a text search parser + with the same name already exists. + + + + + name diff --git a/doc/src/sgml/ref/create_tstemplate.sgml b/doc/src/sgml/ref/create_tstemplate.sgml index 532419c..ac65baf 100644 --- a/doc/src/sgml/ref/create_tstemplate.sgml +++ b/doc/src/sgml/ref/create_tstemplate.sgml @@ -21,7 +21,7 @@ PostgreSQL documentation -CREATE TEXT SEARCH TEMPLATE name ( +CREATE TEXT SEARCH TEMPLATE [ IF NOT EXISTS ] name ( [ INIT = init_function , ] LEXIZE = lexize_function ) @@ -65,6 +65,16 @@ CREATE TEXT SEARCH TEMPLATE name ( + IF NOT EXISTS + + + Do nothing (except issuing a notice) if a text search template with + the same name already exists. + + + + + name diff --git a/doc/src/sgml/ref/create_type.sgml b/doc/src/sgml/ref/create_type.sgml index 606efee..0919da7 100644 --- a/doc/src/sgml/ref/create_type.sgml +++ b/doc/src/sgml/ref/create_type.sgml @@ -21,13 +21,13 @@ PostgreSQL documentation -CREATE TYPE name AS +CREATE TYPE [ IF NOT EXISTS ] name AS ( [ attribute_name data_type [ COLLATE collation ] [, ... ] ] ) -CREATE TYPE name AS ENUM +CREATE TYPE [ IF NOT EXISTS ] name AS ENUM ( [ 'label' [, ... ] ] ) -CREATE TYPE name AS RANGE ( +CREATE TYPE [ IF NOT EXISTS ] name AS RANGE ( SUBTYPE = subtype [ , SUBTYPE_OPCLASS = subtype_operator_class ] [ , COLLATION = collation ] @@ -35,7 +35,7 @@ CREATE TYPE name AS RANGE ( [ , SUBTYPE_DIFF = subtype_diff_function ] ) -CREATE TYPE name ( +CREATE TYPE [ IF NOT EXISTS ] name ( INPUT = input_function, OUTPUT = output_function [ , RECEIVE = receive_function ] @@ -56,7 +56,7 @@ CREATE TYPE name ( [ , COLLATABLE = collatable ] ) -CREATE TYPE name +CREATE TYPE [ IF NOT EXISTS ] name @@ -484,6 +484,16 @@ CREATE TYPE name + IF NOT EXISTS + + + Do nothing (except issuing a notice) if a type with the same name + already exists. + + + + + name diff --git a/src/backend/bootstrap/bootparse.y b/src/backend/bootstrap/bootparse.y index cee72c1..aca9964 100644 --- a/src/backend/bootstrap/bootparse.y +++ b/src/backend/bootstrap/bootparse.y @@ -249,6 +249,7 @@ Boot_CreateStmt: (Datum) 0, false, true, + false, false); elog(DEBUG4, "relation created with OID %u", id); } diff --git a/src/backend/catalog/heap.c b/src/backend/catalog/heap.c index a910f81..347e0c6 100644 --- a/src/backend/catalog/heap.c +++ b/src/backend/catalog/heap.c @@ -964,7 +964,8 @@ AddNewRelationType(const char *typeName, -1, /* typmod */ 0, /* array dimensions for typBaseType */ false, /* Type NOT NULL */ - InvalidOid); /* rowtypes never have a collation */ + InvalidOid, /* rowtypes never have a collation */ + false); /* if not exists flag */ } /* -------------------------------- @@ -1017,7 +1018,8 @@ heap_create_with_catalog(const char *relname, Datum reloptions, bool use_user_acl, bool allow_system_table_mods, - bool is_internal) + bool is_internal, + bool ifNotExists) { Relation pg_class_desc; Relation new_rel_desc; @@ -1042,9 +1044,20 @@ heap_create_with_catalog(const char *relname, */ existing_relid = get_relname_relid(relname, relnamespace); if (existing_relid != InvalidOid) + { + if (ifNotExists) + { + ereport(NOTICE, + (errcode(ERRCODE_DUPLICATE_TABLE), + errmsg("relation \"%s\" already exists, skipping", relname))); + heap_close(pg_class_desc, RowExclusiveLock); + return InvalidOid; + } + ereport(ERROR, (errcode(ERRCODE_DUPLICATE_TABLE), errmsg("relation \"%s\" already exists", relname))); + } /* * Since we are going to create a rowtype as well, also check for @@ -1220,7 +1233,8 @@ heap_create_with_catalog(const char *relname, -1, /* typmod */ 0, /* array dimensions for typBaseType */ false, /* Type NOT NULL */ - InvalidOid); /* rowtypes never have a collation */ + InvalidOid, /* rowtypes never have a collation */ + false); /* if not exists */ pfree(relarrayname); } diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c index 0275240..40eb62b 100644 --- a/src/backend/catalog/index.c +++ b/src/backend/catalog/index.c @@ -696,7 +696,8 @@ index_create(Relation heapRelation, bool allow_system_table_mods, bool skip_build, bool concurrent, - bool is_internal) + bool is_internal, + bool ifNotExists) { Oid heapRelationId = RelationGetRelid(heapRelation); Relation pg_class; @@ -772,10 +773,22 @@ index_create(Relation heapRelation, elog(ERROR, "shared relations must be placed in pg_global tablespace"); if (get_relname_relid(indexRelationName, namespaceId)) + { + if (ifNotExists) + { + ereport(NOTICE, + (errcode(ERRCODE_DUPLICATE_TABLE), + errmsg("relation \"%s\" already exists, skipping", + indexRelationName))); + heap_close(pg_class, RowExclusiveLock); + return InvalidOid; + } + ereport(ERROR, (errcode(ERRCODE_DUPLICATE_TABLE), errmsg("relation \"%s\" already exists", indexRelationName))); + } /* * construct tuple descriptor for index tuples diff --git a/src/backend/catalog/pg_aggregate.c b/src/backend/catalog/pg_aggregate.c index 9dbec50..67ff7b6 100644 --- a/src/backend/catalog/pg_aggregate.c +++ b/src/backend/catalog/pg_aggregate.c @@ -56,7 +56,8 @@ AggregateCreate(const char *aggName, List *aggsortopName, Oid aggTransType, int32 aggTransSpace, - const char *agginitval) + const char *agginitval, + bool aggIfNotExists) { Relation aggdesc; HeapTuple tup; @@ -257,7 +258,11 @@ AggregateCreate(const char *aggName, parameterDefaults, /* parameterDefaults */ PointerGetDatum(NULL), /* proconfig */ 1, /* procost */ - 0); /* prorows */ + 0, /* prorows */ + aggIfNotExists); /* if not exists */ + + if (!OidIsValid(procOid)) + return InvalidOid; /* * Okay to create the pg_aggregate entry. diff --git a/src/backend/catalog/pg_operator.c b/src/backend/catalog/pg_operator.c index 3c4fedb..47fcb4f 100644 --- a/src/backend/catalog/pg_operator.c +++ b/src/backend/catalog/pg_operator.c @@ -336,7 +336,8 @@ OperatorCreate(const char *operatorName, Oid restrictionId, Oid joinId, bool canMerge, - bool canHash) + bool canHash, + bool ifNotExists) { Relation pg_operator_desc; HeapTuple tup; @@ -417,10 +418,22 @@ OperatorCreate(const char *operatorName, &operatorAlreadyDefined); if (operatorAlreadyDefined) + { + /* skip if already exists */ + if (ifNotExists) + { + ereport(NOTICE, + (errcode(ERRCODE_DUPLICATE_FUNCTION), + errmsg("operator %s already exists, skipping", + operatorName))); + return InvalidOid; + } + ereport(ERROR, (errcode(ERRCODE_DUPLICATE_FUNCTION), errmsg("operator %s already exists", operatorName))); + } /* * At this point, if operatorObjectId is not InvalidOid then we are diff --git a/src/backend/catalog/pg_proc.c b/src/backend/catalog/pg_proc.c index 05a550e..4b9c5f3 100644 --- a/src/backend/catalog/pg_proc.c +++ b/src/backend/catalog/pg_proc.c @@ -88,7 +88,8 @@ ProcedureCreate(const char *procedureName, List *parameterDefaults, Datum proconfig, float4 procost, - float4 prorows) + float4 prorows, + bool ifNotExists) { Oid retval; int parameterCount; @@ -388,10 +389,23 @@ ProcedureCreate(const char *procedureName, bool isnull; if (!replace) + { + if (ifNotExists) + { + ereport(NOTICE, + (errcode(ERRCODE_DUPLICATE_FUNCTION), + errmsg("function \"%s\" already exists with same argument types, skipping", + procedureName))); + ReleaseSysCache(oldtup); + heap_close(rel, RowExclusiveLock); + return InvalidOid; + } + ereport(ERROR, (errcode(ERRCODE_DUPLICATE_FUNCTION), - errmsg("function \"%s\" already exists with same argument types", - procedureName))); + errmsg("function \"%s\" already exists with same argument types", + procedureName))); + } if (!pg_proc_ownercheck(HeapTupleGetOid(oldtup), proowner)) aclcheck_error(ACLCHECK_NOT_OWNER, ACL_KIND_PROC, procedureName); diff --git a/src/backend/catalog/pg_type.c b/src/backend/catalog/pg_type.c index 23ac3dd..03d8216 100644 --- a/src/backend/catalog/pg_type.c +++ b/src/backend/catalog/pg_type.c @@ -215,7 +215,8 @@ TypeCreate(Oid newTypeOid, int32 typeMod, int32 typNDims, /* Array dimensions for baseType */ bool typeNotNull, - Oid typeCollation) + Oid typeCollation, + bool ifNotExists) { Relation pg_type_desc; Oid typeObjectId; @@ -397,9 +398,21 @@ TypeCreate(Oid newTypeOid, * shell type, however. */ if (((Form_pg_type) GETSTRUCT(tup))->typisdefined) + { + /* skip if already exists */ + if (ifNotExists) + { + ereport(NOTICE, + (errcode(ERRCODE_DUPLICATE_OBJECT), + errmsg("type \"%s\" already exists, skipping", typeName))); + heap_close(pg_type_desc, RowExclusiveLock); + return InvalidOid; + } + ereport(ERROR, (errcode(ERRCODE_DUPLICATE_OBJECT), errmsg("type \"%s\" already exists", typeName))); + } /* * shell type must have been created by same owner diff --git a/src/backend/catalog/toasting.c b/src/backend/catalog/toasting.c index 385d64d..441342e 100644 --- a/src/backend/catalog/toasting.c +++ b/src/backend/catalog/toasting.c @@ -228,7 +228,8 @@ create_toast_table(Relation rel, Oid toastOid, Oid toastIndexOid, Datum reloptio reloptions, false, true, - true); + true, + false); Assert(toast_relid != InvalidOid); /* make the toast relation visible, else heap_open will fail */ @@ -281,7 +282,8 @@ create_toast_table(Relation rel, Oid toastOid, Oid toastIndexOid, Datum reloptio rel->rd_rel->reltablespace, collationObjectId, classObjectId, coloptions, (Datum) 0, true, false, false, false, - true, false, false, true); + true, false, false, true, + false); heap_close(toast_rel, NoLock); diff --git a/src/backend/commands/aggregatecmds.c b/src/backend/commands/aggregatecmds.c index 6fc3e04..365b725 100644 --- a/src/backend/commands/aggregatecmds.c +++ b/src/backend/commands/aggregatecmds.c @@ -50,7 +50,7 @@ */ Oid DefineAggregate(List *name, List *args, bool oldstyle, List *parameters, - const char *queryString) + const char *queryString, bool ifNotExists) { char *aggName; Oid aggNamespace; @@ -250,7 +250,8 @@ DefineAggregate(List *name, List *args, bool oldstyle, List *parameters, transfuncName, /* step function name */ finalfuncName, /* final function name */ sortoperatorName, /* sort operator name */ - transTypeId, /* transition data type */ + transTypeId, /* transition data type */ transSpace, /* transition space */ - initval); /* initial condition */ + initval, /* initial condition */ + ifNotExists); /* if not exists flag */ } diff --git a/src/backend/commands/cluster.c b/src/backend/commands/cluster.c index f6a5bfe..96fc6a0 100644 --- a/src/backend/commands/cluster.c +++ b/src/backend/commands/cluster.c @@ -686,7 +686,8 @@ make_new_heap(Oid OIDOldHeap, Oid NewTableSpace, bool forcetemp, reloptions, false, true, - true); + true, + false); Assert(OIDNewHeap != InvalidOid); ReleaseSysCache(tuple); diff --git a/src/backend/commands/event_trigger.c b/src/backend/commands/event_trigger.c index 328e2a8..8e9a689 100644 --- a/src/backend/commands/event_trigger.c +++ b/src/backend/commands/event_trigger.c @@ -189,10 +189,22 @@ CreateEventTrigger(CreateEventTrigStmt *stmt) */ tuple = SearchSysCache1(EVENTTRIGGERNAME, CStringGetDatum(stmt->trigname)); if (HeapTupleIsValid(tuple)) + { + if (stmt->if_not_exists) + { + ereport(NOTICE, + (errcode(ERRCODE_DUPLICATE_OBJECT), + errmsg("event trigger \"%s\" already exists, skipping", + stmt->trigname))); + ReleaseSysCache(tuple); + return InvalidOid; + } + ereport(ERROR, (errcode(ERRCODE_DUPLICATE_OBJECT), errmsg("event trigger \"%s\" already exists", stmt->trigname))); + } /* Find and validate the trigger function. */ funcoid = LookupFuncName(stmt->funcname, 0, NULL, false); diff --git a/src/backend/commands/functioncmds.c b/src/backend/commands/functioncmds.c index ca754b4..ef7e6b6 100644 --- a/src/backend/commands/functioncmds.c +++ b/src/backend/commands/functioncmds.c @@ -1007,7 +1007,8 @@ CreateFunction(CreateFunctionStmt *stmt, const char *queryString) parameterDefaults, PointerGetDatum(proconfig), procost, - prorows); + prorows, + false); } @@ -1522,8 +1523,6 @@ CreateCast(CreateCastStmt *stmt) break; } - relation = heap_open(CastRelationId, RowExclusiveLock); - /* * Check for duplicate. This is just to give a friendly error message, * the unique index would catch it anyway (so no need to sweat about race @@ -1533,11 +1532,27 @@ CreateCast(CreateCastStmt *stmt) ObjectIdGetDatum(sourcetypeid), ObjectIdGetDatum(targettypeid)); if (HeapTupleIsValid(tuple)) + { + /* skip if already exists */ + if (stmt->if_not_exists) + { + ereport(NOTICE, + (errcode(ERRCODE_DUPLICATE_OBJECT), + errmsg("cast from type %s to type %s already exists, skipping", + format_type_be(sourcetypeid), + format_type_be(targettypeid)))); + ReleaseSysCache(tuple); + return InvalidOid; + } + ereport(ERROR, (errcode(ERRCODE_DUPLICATE_OBJECT), errmsg("cast from type %s to type %s already exists", format_type_be(sourcetypeid), format_type_be(targettypeid)))); + } + + relation = heap_open(CastRelationId, RowExclusiveLock); /* ready to go */ values[Anum_pg_cast_castsource - 1] = ObjectIdGetDatum(sourcetypeid); diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.c index 2155252..58e5a4e 100644 --- a/src/backend/commands/indexcmds.c +++ b/src/backend/commands/indexcmds.c @@ -600,7 +600,14 @@ DefineIndex(IndexStmt *stmt, stmt->isconstraint, stmt->deferrable, stmt->initdeferred, allowSystemTableMods, skip_build || stmt->concurrent, - stmt->concurrent, !check_rights); + stmt->concurrent, !check_rights, + stmt->if_not_exists); + + if (!OidIsValid(indexRelationId)) + { + heap_close(rel, NoLock); + return indexRelationId; + } /* Add any requested comment */ if (stmt->idxcomment != NULL) diff --git a/src/backend/commands/operatorcmds.c b/src/backend/commands/operatorcmds.c index 4692b08..c8d3363 100644 --- a/src/backend/commands/operatorcmds.c +++ b/src/backend/commands/operatorcmds.c @@ -60,7 +60,7 @@ * 'parameters' is a list of DefElem */ Oid -DefineOperator(List *names, List *parameters) +DefineOperator(List *names, List *parameters, bool ifNotExists) { char *oprName; Oid oprNamespace; @@ -306,7 +306,8 @@ DefineOperator(List *names, List *parameters) restrictionOid, /* optional restrict. sel. procedure */ joinOid, /* optional join sel. procedure name */ canMerge, /* operator merges */ - canHash); /* operator hashes */ + canHash, /* operator hashes */ + ifNotExists); /* if not exists flag */ } /* diff --git a/src/backend/commands/proclang.c b/src/backend/commands/proclang.c index b7be1f7..28f22fc 100644 --- a/src/backend/commands/proclang.c +++ b/src/backend/commands/proclang.c @@ -141,7 +141,8 @@ CreateProceduralLanguage(CreatePLangStmt *stmt) NIL, PointerGetDatum(NULL), 1, - 0); + 0, + false); } /* @@ -178,7 +179,8 @@ CreateProceduralLanguage(CreatePLangStmt *stmt) NIL, PointerGetDatum(NULL), 1, - 0); + 0, + false); } } else @@ -218,7 +220,8 @@ CreateProceduralLanguage(CreatePLangStmt *stmt) NIL, PointerGetDatum(NULL), 1, - 0); + 0, + false); } } else diff --git a/src/backend/commands/sequence.c b/src/backend/commands/sequence.c index 67b8a5d..7e6057c 100644 --- a/src/backend/commands/sequence.c +++ b/src/backend/commands/sequence.c @@ -209,9 +209,13 @@ DefineSequence(CreateSeqStmt *seq) stmt->options = NIL; stmt->oncommit = ONCOMMIT_NOOP; stmt->tablespacename = NULL; - stmt->if_not_exists = false; + stmt->if_not_exists = seq->if_not_exists; seqoid = DefineRelation(stmt, RELKIND_SEQUENCE, seq->ownerId); + + if (seq->if_not_exists && !OidIsValid(seqoid)) + return InvalidOid; + Assert(seqoid != InvalidOid); rel = heap_open(seqoid, AccessExclusiveLock); diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index 0b31f55..fc6b69a 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -644,7 +644,11 @@ DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId) reloptions, true, allowSystemTableMods, - false); + false, + stmt->if_not_exists); + + if (!OidIsValid(relationId)) + return relationId; /* Store inheritance information for new rel. */ StoreCatalogInheritance(relationId, inheritOids); diff --git a/src/backend/commands/tsearchcmds.c b/src/backend/commands/tsearchcmds.c index bb9a0b4..13b6678 100644 --- a/src/backend/commands/tsearchcmds.c +++ b/src/backend/commands/tsearchcmds.c @@ -168,7 +168,7 @@ makeParserDependencies(HeapTuple tuple) * CREATE TEXT SEARCH PARSER */ Oid -DefineTSParser(List *names, List *parameters) +DefineTSParser(List *names, List *parameters, bool ifNotExists) { char *prsname; ListCell *pl; @@ -188,6 +188,31 @@ DefineTSParser(List *names, List *parameters) /* Convert list of names to a name and namespace */ namespaceoid = QualifiedNameGetCreationNamespace(names, &prsname); + /* Check if text search parser already exists */ + prsOid = GetSysCacheOid2(TSPARSERNAMENSP, + CStringGetDatum(prsname), + ObjectIdGetDatum(namespaceoid)); + + if (OidIsValid(prsOid)) + { + /* skip if already exists */ + if (ifNotExists) + { + ereport(NOTICE, + (errcode(ERRCODE_DUPLICATE_OBJECT), + errmsg("text search parser \"%s\".\"%s\" already exists, skipping", + get_namespace_name(namespaceoid), + prsname))); + return InvalidOid; + } + + ereport(ERROR, + (errcode(ERRCODE_DUPLICATE_OBJECT), + errmsg("text search parser \"%s\".\"%s\" already exists", + get_namespace_name(namespaceoid), + prsname))); + } + /* initialize tuple fields with name/namespace */ memset(values, 0, sizeof(values)); memset(nulls, false, sizeof(nulls)); @@ -398,7 +423,7 @@ verify_dictoptions(Oid tmplId, List *dictoptions) * CREATE TEXT SEARCH DICTIONARY */ Oid -DefineTSDictionary(List *names, List *parameters) +DefineTSDictionary(List *names, List *parameters, bool ifNotExists) { ListCell *pl; Relation dictRel; @@ -412,15 +437,43 @@ DefineTSDictionary(List *names, List *parameters) Oid namespaceoid; AclResult aclresult; char *dictname; + char *dictnamespace; /* Convert list of names to a name and namespace */ namespaceoid = QualifiedNameGetCreationNamespace(names, &dictname); + /* Get namespace name */ + dictnamespace = get_namespace_name(namespaceoid); + /* Check we have creation rights in target namespace */ aclresult = pg_namespace_aclcheck(namespaceoid, GetUserId(), ACL_CREATE); if (aclresult != ACLCHECK_OK) aclcheck_error(aclresult, ACL_KIND_NAMESPACE, - get_namespace_name(namespaceoid)); + dictnamespace); + + /* Check if text search dictionary already exists */ + dictOid = GetSysCacheOid2(TSDICTNAMENSP, + CStringGetDatum(dictname), + ObjectIdGetDatum(namespaceoid)); + + if (OidIsValid(dictOid)) + { + if (ifNotExists) + { + ereport(NOTICE, + (errcode(ERRCODE_DUPLICATE_OBJECT), + errmsg("text search dictionary \"%s\".\"%s\" already exists, skipping", + dictnamespace, + dictname))); + return InvalidOid; + } + + ereport(ERROR, + (errcode(ERRCODE_DUPLICATE_OBJECT), + errmsg("text search dictionary \"%s\".\"%s\" already exists", + dictnamespace, + dictname))); + } /* * loop over the definition list and extract the information we need. @@ -716,7 +769,7 @@ makeTSTemplateDependencies(HeapTuple tuple) * CREATE TEXT SEARCH TEMPLATE */ Oid -DefineTSTemplate(List *names, List *parameters) +DefineTSTemplate(List *names, List *parameters, bool ifNotExists) { ListCell *pl; Relation tmplRel; @@ -737,6 +790,30 @@ DefineTSTemplate(List *names, List *parameters) /* Convert list of names to a name and namespace */ namespaceoid = QualifiedNameGetCreationNamespace(names, &tmplname); + /* Check if text search template already exists */ + tmplOid = GetSysCacheOid2(TSTEMPLATENAMENSP, + CStringGetDatum(tmplname), + ObjectIdGetDatum(namespaceoid)); + + if (OidIsValid(tmplOid)) + { + if (ifNotExists) + { + ereport(NOTICE, + (errcode(ERRCODE_DUPLICATE_OBJECT), + errmsg("text search template \"%s\".\"%s\" already exists, skipping", + get_namespace_name(namespaceoid), + tmplname))); + return InvalidOid; + } + + ereport(ERROR, + (errcode(ERRCODE_DUPLICATE_OBJECT), + errmsg("text search template \"%s\".\"%s\" already exists", + get_namespace_name(namespaceoid), + tmplname))); + } + for (i = 0; i < Natts_pg_ts_template; i++) { nulls[i] = false; @@ -946,7 +1023,7 @@ makeConfigurationDependencies(HeapTuple tuple, bool removeOld, * CREATE TEXT SEARCH CONFIGURATION */ Oid -DefineTSConfiguration(List *names, List *parameters) +DefineTSConfiguration(List *names, List *parameters, bool ifNotExists) { Relation cfgRel; Relation mapRel = NULL; @@ -956,6 +1033,7 @@ DefineTSConfiguration(List *names, List *parameters) AclResult aclresult; Oid namespaceoid; char *cfgname; + char *cfgnamespace; NameData cname; Oid sourceOid = InvalidOid; Oid prsOid = InvalidOid; @@ -965,11 +1043,38 @@ DefineTSConfiguration(List *names, List *parameters) /* Convert list of names to a name and namespace */ namespaceoid = QualifiedNameGetCreationNamespace(names, &cfgname); + /* Get namespace name */ + cfgnamespace = get_namespace_name(namespaceoid); + /* Check we have creation rights in target namespace */ aclresult = pg_namespace_aclcheck(namespaceoid, GetUserId(), ACL_CREATE); if (aclresult != ACLCHECK_OK) aclcheck_error(aclresult, ACL_KIND_NAMESPACE, - get_namespace_name(namespaceoid)); + cfgnamespace); + + /* Check if text search configuration already exists */ + cfgOid = GetSysCacheOid2(TSCONFIGNAMENSP, + CStringGetDatum(cfgname), + ObjectIdGetDatum(namespaceoid)); + + if (OidIsValid(cfgOid)) + { + if (ifNotExists) + { + ereport(NOTICE, + (errcode(ERRCODE_DUPLICATE_OBJECT), + errmsg("text search configuration \"%s\".\"%s\" already exists, skipping", + cfgnamespace, + cfgname))); + return InvalidOid; + } + + ereport(ERROR, + (errcode(ERRCODE_DUPLICATE_OBJECT), + errmsg("text search configuration \"%s\".\"%s\" already exists", + cfgnamespace, + cfgname))); + } /* * loop over the definition list and extract the information we need. diff --git a/src/backend/commands/typecmds.c b/src/backend/commands/typecmds.c index d4a14ca..2a00dfb 100644 --- a/src/backend/commands/typecmds.c +++ b/src/backend/commands/typecmds.c @@ -114,7 +114,7 @@ static char *domainAddConstraint(Oid domainOid, Oid domainNamespace, * Registers a new base type. */ Oid -DefineType(List *names, List *parameters) +DefineType(List *names, List *parameters, bool ifNotExists) { char *typeName; Oid typeNamespace; @@ -233,9 +233,20 @@ DefineType(List *names, List *parameters) { /* Complain if dummy CREATE TYPE and entry already exists */ if (parameters == NIL) + { + /* skip if already exists */ + if (ifNotExists) + { + ereport(NOTICE, + (errcode(ERRCODE_DUPLICATE_OBJECT), + errmsg("type \"%s\" already exists, skipping", typeName))); + return InvalidOid; + } + ereport(ERROR, (errcode(ERRCODE_DUPLICATE_OBJECT), errmsg("type \"%s\" already exists", typeName))); + } } /* Extract the parameters from the parameter list */ @@ -585,7 +596,11 @@ DefineType(List *names, List *parameters) -1, /* typMod (Domains only) */ 0, /* Array Dimensions of typbasetype */ false, /* Type NOT NULL */ - collation); /* type's collation */ + collation, /* type's collation */ + ifNotExists); /* if not exists flag */ + + if (!OidIsValid(typoid)) + return typoid; /* * Create the array type that goes with it. @@ -621,11 +636,12 @@ DefineType(List *names, List *parameters) NULL, /* binary default isn't sent either */ false, /* never passed by value */ alignment, /* see above */ - 'x', /* ARRAY is always toastable */ - -1, /* typMod (Domains only) */ - 0, /* Array dimensions of typbasetype */ - false, /* Type NOT NULL */ - collation); /* type's collation */ + 'x', /* ARRAY is always toastable */ + -1, /* typMod (Domains only) */ + 0, /* Array dimensions of typbasetype */ + false, /* Type NOT NULL */ + collation, /* type's collation */ + ifNotExists); /* if not exists flag */ pfree(array_type); @@ -733,9 +749,19 @@ DefineDomain(CreateDomainStmt *stmt) if (OidIsValid(old_type_oid)) { if (!moveArrayTypeName(old_type_oid, domainName, domainNamespace)) + { + if (stmt->if_not_exists) + { + ereport(NOTICE, + (errcode(ERRCODE_DUPLICATE_OBJECT), + errmsg("type \"%s\" already exists, skipping", domainName))); + return InvalidOid; + } + ereport(ERROR, (errcode(ERRCODE_DUPLICATE_OBJECT), errmsg("type \"%s\" already exists", domainName))); + } } /* @@ -1011,7 +1037,14 @@ DefineDomain(CreateDomainStmt *stmt) basetypeMod, /* typeMod value */ typNDims, /* Array dimensions for base type */ typNotNull, /* Type NOT NULL */ - domaincoll); /* type's collation */ + domaincoll, /* type's collation */ + stmt->if_not_exists); /* if not exists flag */ + + if (!OidIsValid(domainoid)) + { + ReleaseSysCache(typeTup); + return domainoid; + } /* * Process constraints which refer to the domain ID returned by TypeCreate @@ -1084,9 +1117,20 @@ DefineEnum(CreateEnumStmt *stmt) if (OidIsValid(old_type_oid)) { if (!moveArrayTypeName(old_type_oid, enumName, enumNamespace)) + { + if (stmt->if_not_exists) + { + ereport(NOTICE, + (errcode(ERRCODE_DUPLICATE_OBJECT), + errmsg("type \"%s\" already exists, skipping", enumName))); + return InvalidOid; + } + ereport(ERROR, (errcode(ERRCODE_DUPLICATE_OBJECT), errmsg("type \"%s\" already exists", enumName))); + + } } enumArrayOid = AssignTypeArrayOid(); @@ -1123,7 +1167,11 @@ DefineEnum(CreateEnumStmt *stmt) -1, /* typMod (Domains only) */ 0, /* Array dimensions of typbasetype */ false, /* Type NOT NULL */ - InvalidOid); /* type's collation */ + InvalidOid, /* type's collation */ + stmt->if_not_exists); /* if not exists flag */ + + if (!OidIsValid(enumTypeOid)) + return enumTypeOid; /* Enter the enum's values into pg_enum */ EnumValuesCreate(enumTypeOid, stmt->vals); @@ -1163,7 +1211,8 @@ DefineEnum(CreateEnumStmt *stmt) -1, /* typMod (Domains only) */ 0, /* Array dimensions of typbasetype */ false, /* Type NOT NULL */ - InvalidOid); /* type's collation */ + InvalidOid, /* type's collation */ + stmt->if_not_exists); /* if not exists flag */ pfree(enumArrayName); @@ -1302,9 +1351,19 @@ DefineRange(CreateRangeStmt *stmt) if (moveArrayTypeName(typoid, typeName, typeNamespace)) typoid = InvalidOid; else + { + if (stmt->if_not_exists) + { + ereport(NOTICE, + (errcode(ERRCODE_DUPLICATE_OBJECT), + errmsg("type \"%s\" already exists, skipping", typeName))); + return InvalidOid; + } + ereport(ERROR, (errcode(ERRCODE_DUPLICATE_OBJECT), errmsg("type \"%s\" already exists", typeName))); + } } /* @@ -1457,7 +1516,11 @@ DefineRange(CreateRangeStmt *stmt) -1, /* typMod (Domains only) */ 0, /* Array dimensions of typbasetype */ false, /* Type NOT NULL */ - InvalidOid); /* type's collation (ranges never have one) */ + InvalidOid, /* type's collation (ranges never have one) */ + stmt->if_not_exists); /* if not exists flag */ + + if (!OidIsValid(typoid)) + return typoid; /* Create the entry in pg_range */ RangeCreate(typoid, rangeSubtype, rangeCollation, rangeSubOpclass, @@ -1498,7 +1561,8 @@ DefineRange(CreateRangeStmt *stmt) -1, /* typMod (Domains only) */ 0, /* Array dimensions of typbasetype */ false, /* Type NOT NULL */ - InvalidOid); /* typcollation */ + InvalidOid, /* typcollation */ + stmt->if_not_exists); /* if not exists flag */ pfree(rangeArrayName); @@ -1569,7 +1633,8 @@ makeRangeConstructors(const char *name, Oid namespace, NIL, /* parameterDefaults */ PointerGetDatum(NULL), /* proconfig */ 1.0, /* procost */ - 0.0); /* prorows */ + 0.0, /* prorows */ + false); /* if not exists */ /* * Make the constructors internally-dependent on the range type so @@ -2018,7 +2083,7 @@ AssignTypeArrayOid(void) *------------------------------------------------------------------- */ Oid -DefineCompositeType(RangeVar *typevar, List *coldeflist) +DefineCompositeType(RangeVar *typevar, List *coldeflist, bool ifNotExists) { CreateStmt *createStmt = makeNode(CreateStmt); Oid old_type_oid; @@ -2054,9 +2119,19 @@ DefineCompositeType(RangeVar *typevar, List *coldeflist) if (OidIsValid(old_type_oid)) { if (!moveArrayTypeName(old_type_oid, createStmt->relation->relname, typeNamespace)) + { + if (ifNotExists) + { + ereport(NOTICE, + (errcode(ERRCODE_DUPLICATE_OBJECT), + errmsg("type \"%s\" already exists, skipping", createStmt->relation->relname))); + return InvalidOid; + } + ereport(ERROR, (errcode(ERRCODE_DUPLICATE_OBJECT), errmsg("type \"%s\" already exists", createStmt->relation->relname))); + } } /* diff --git a/src/backend/commands/user.c b/src/backend/commands/user.c index e101a86..b97f5c8 100644 --- a/src/backend/commands/user.c +++ b/src/backend/commands/user.c @@ -306,10 +306,22 @@ CreateRole(CreateRoleStmt *stmt) pg_authid_dsc = RelationGetDescr(pg_authid_rel); if (OidIsValid(get_role_oid(stmt->role, true))) + { + if (stmt->if_not_exists) + { + ereport(NOTICE, + (errcode(ERRCODE_DUPLICATE_OBJECT), + errmsg("role \"%s\" already exists, skipping", + stmt->role))); + heap_close(pg_authid_rel, NoLock); + return InvalidOid; + } + ereport(ERROR, (errcode(ERRCODE_DUPLICATE_OBJECT), errmsg("role \"%s\" already exists", stmt->role))); + } /* Convert validuntil to internal form */ if (validUntil) diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c index 1733da6..dfc632c 100644 --- a/src/backend/nodes/copyfuncs.c +++ b/src/backend/nodes/copyfuncs.c @@ -2784,6 +2784,7 @@ _copyDefineStmt(const DefineStmt *from) COPY_NODE_FIELD(defnames); COPY_NODE_FIELD(args); COPY_NODE_FIELD(definition); + COPY_SCALAR_FIELD(if_not_exists); return newnode; } @@ -2877,6 +2878,7 @@ _copyIndexStmt(const IndexStmt *from) COPY_SCALAR_FIELD(deferrable); COPY_SCALAR_FIELD(initdeferred); COPY_SCALAR_FIELD(concurrent); + COPY_SCALAR_FIELD(if_not_exists); return newnode; } @@ -3043,6 +3045,7 @@ _copyCompositeTypeStmt(const CompositeTypeStmt *from) COPY_NODE_FIELD(typevar); COPY_NODE_FIELD(coldeflist); + COPY_SCALAR_FIELD(if_not_exists); return newnode; } @@ -3054,6 +3057,7 @@ _copyCreateEnumStmt(const CreateEnumStmt *from) COPY_NODE_FIELD(typeName); COPY_NODE_FIELD(vals); + COPY_SCALAR_FIELD(if_not_exists); return newnode; } @@ -3065,6 +3069,7 @@ _copyCreateRangeStmt(const CreateRangeStmt *from) COPY_NODE_FIELD(typeName); COPY_NODE_FIELD(params); + COPY_SCALAR_FIELD(if_not_exists); return newnode; } @@ -3117,6 +3122,7 @@ _copyCreateDomainStmt(const CreateDomainStmt *from) COPY_NODE_FIELD(typeName); COPY_NODE_FIELD(collClause); COPY_NODE_FIELD(constraints); + COPY_SCALAR_FIELD(if_not_exists); return newnode; } @@ -3692,6 +3698,7 @@ _copyCreateCastStmt(const CreateCastStmt *from) COPY_NODE_FIELD(func); COPY_SCALAR_FIELD(context); COPY_SCALAR_FIELD(inout); + COPY_SCALAR_FIELD(if_not_exists); return newnode; } diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c index 7b29812..9a8f220 100644 --- a/src/backend/nodes/equalfuncs.c +++ b/src/backend/nodes/equalfuncs.c @@ -1125,6 +1125,7 @@ _equalDefineStmt(const DefineStmt *a, const DefineStmt *b) COMPARE_NODE_FIELD(defnames); COMPARE_NODE_FIELD(args); COMPARE_NODE_FIELD(definition); + COMPARE_SCALAR_FIELD(if_not_exists); return true; } @@ -1346,6 +1347,7 @@ _equalCompositeTypeStmt(const CompositeTypeStmt *a, const CompositeTypeStmt *b) { COMPARE_NODE_FIELD(typevar); COMPARE_NODE_FIELD(coldeflist); + COMPARE_SCALAR_FIELD(if_not_exists); return true; } @@ -1355,6 +1357,7 @@ _equalCreateEnumStmt(const CreateEnumStmt *a, const CreateEnumStmt *b) { COMPARE_NODE_FIELD(typeName); COMPARE_NODE_FIELD(vals); + COMPARE_SCALAR_FIELD(if_not_exists); return true; } @@ -1364,6 +1367,7 @@ _equalCreateRangeStmt(const CreateRangeStmt *a, const CreateRangeStmt *b) { COMPARE_NODE_FIELD(typeName); COMPARE_NODE_FIELD(params); + COMPARE_SCALAR_FIELD(if_not_exists); return true; } @@ -1408,6 +1412,7 @@ _equalCreateDomainStmt(const CreateDomainStmt *a, const CreateDomainStmt *b) COMPARE_NODE_FIELD(typeName); COMPARE_NODE_FIELD(collClause); COMPARE_NODE_FIELD(constraints); + COMPARE_SCALAR_FIELD(if_not_exists); return true; } @@ -1552,6 +1557,7 @@ _equalCreateSeqStmt(const CreateSeqStmt *a, const CreateSeqStmt *b) COMPARE_NODE_FIELD(sequence); COMPARE_NODE_FIELD(options); COMPARE_SCALAR_FIELD(ownerId); + COMPARE_SCALAR_FIELD(if_not_exists); return true; } @@ -1893,6 +1899,7 @@ _equalCreateCastStmt(const CreateCastStmt *a, const CreateCastStmt *b) COMPARE_NODE_FIELD(func); COMPARE_SCALAR_FIELD(context); COMPARE_SCALAR_FIELD(inout); + COMPARE_SCALAR_FIELD(if_not_exists); return true; } diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index 11f6291..7cad3e3 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -836,8 +836,19 @@ CreateRoleStmt: n->stmt_type = ROLESTMT_ROLE; n->role = $3; n->options = $5; + n->if_not_exists = false; $$ = (Node *)n; } + | CREATE ROLE IF_P NOT EXISTS RoleId opt_with OptRoleList + { + CreateRoleStmt *n = makeNode(CreateRoleStmt); + n->stmt_type = ROLESTMT_ROLE; + n->role = $6; + n->options = $8; + n->if_not_exists = true; + $$ = (Node *)n; + } + ; @@ -3373,6 +3384,17 @@ CreateSeqStmt: n->sequence = $4; n->options = $5; n->ownerId = InvalidOid; + n->if_not_exists = false; + $$ = (Node *)n; + } + | CREATE OptTemp SEQUENCE IF_P NOT EXISTS qualified_name OptSeqOptList + { + CreateSeqStmt *n = makeNode(CreateSeqStmt); + $7->relpersistence = $2; + n->sequence = $7; + n->options = $8; + n->ownerId = InvalidOid; + n->if_not_exists = true; $$ = (Node *)n; } ; @@ -4546,6 +4568,18 @@ CreateEventTrigStmt: n->eventname = $6; n->whenclause = NULL; n->funcname = $9; + n->if_not_exists = false; + $$ = (Node *)n; + } + | CREATE EVENT TRIGGER IF_P NOT EXISTS name ON ColLabel + EXECUTE PROCEDURE func_name '(' ')' + { + CreateEventTrigStmt *n = makeNode(CreateEventTrigStmt); + n->trigname = $7; + n->eventname = $9; + n->whenclause = NULL; + n->funcname = $12; + n->if_not_exists = true; $$ = (Node *)n; } | CREATE EVENT TRIGGER name ON ColLabel @@ -4557,6 +4591,19 @@ CreateEventTrigStmt: n->eventname = $6; n->whenclause = $8; n->funcname = $11; + n->if_not_exists = false; + $$ = (Node *)n; + } + | CREATE EVENT TRIGGER IF_P NOT EXISTS name ON ColLabel + WHEN event_trigger_when_list + EXECUTE PROCEDURE func_name '(' ')' + { + CreateEventTrigStmt *n = makeNode(CreateEventTrigStmt); + n->trigname = $7; + n->eventname = $9; + n->whenclause = $11; + n->funcname = $14; + n->if_not_exists = true; $$ = (Node *)n; } ; @@ -4657,6 +4704,18 @@ DefineStmt: n->defnames = $3; n->args = $4; n->definition = $5; + n->if_not_exists = false; + $$ = (Node *)n; + } + | CREATE AGGREGATE IF_P NOT EXISTS func_name aggr_args definition + { + DefineStmt *n = makeNode(DefineStmt); + n->kind = OBJECT_AGGREGATE; + n->oldstyle = false; + n->defnames = $6; + n->args = $7; + n->definition = $8; + n->if_not_exists = true; $$ = (Node *)n; } | CREATE AGGREGATE func_name old_aggr_definition @@ -4668,6 +4727,19 @@ DefineStmt: n->defnames = $3; n->args = NIL; n->definition = $4; + n->if_not_exists = false; + $$ = (Node *)n; + } + | CREATE AGGREGATE IF_P NOT EXISTS func_name old_aggr_definition + { + /* old-style (pre-8.2) syntax for CREATE AGGREGATE */ + DefineStmt *n = makeNode(DefineStmt); + n->kind = OBJECT_AGGREGATE; + n->oldstyle = true; + n->defnames = $6; + n->args = NIL; + n->definition = $7; + n->if_not_exists = true; $$ = (Node *)n; } | CREATE OPERATOR any_operator definition @@ -4678,6 +4750,18 @@ DefineStmt: n->defnames = $3; n->args = NIL; n->definition = $4; + n->if_not_exists = false; + $$ = (Node *)n; + } + | CREATE OPERATOR IF_P NOT EXISTS any_operator definition + { + DefineStmt *n = makeNode(DefineStmt); + n->kind = OBJECT_OPERATOR; + n->oldstyle = false; + n->defnames = $6; + n->args = NIL; + n->definition = $7; + n->if_not_exists = true; $$ = (Node *)n; } | CREATE TYPE_P any_name definition @@ -4688,6 +4772,18 @@ DefineStmt: n->defnames = $3; n->args = NIL; n->definition = $4; + n->if_not_exists = false; + $$ = (Node *)n; + } + | CREATE TYPE_P IF_P NOT EXISTS any_name definition + { + DefineStmt *n = makeNode(DefineStmt); + n->kind = OBJECT_TYPE; + n->oldstyle = false; + n->defnames = $6; + n->args = NIL; + n->definition = $7; + n->if_not_exists = true; $$ = (Node *)n; } | CREATE TYPE_P any_name @@ -4699,6 +4795,19 @@ DefineStmt: n->defnames = $3; n->args = NIL; n->definition = NIL; + n->if_not_exists = false; + $$ = (Node *)n; + } + | CREATE TYPE_P IF_P NOT EXISTS any_name + { + /* Shell type (identified by lack of definition) */ + DefineStmt *n = makeNode(DefineStmt); + n->kind = OBJECT_TYPE; + n->oldstyle = false; + n->defnames = $6; + n->args = NIL; + n->definition = NIL; + n->if_not_exists = true; $$ = (Node *)n; } | CREATE TYPE_P any_name AS '(' OptTableFuncElementList ')' @@ -4708,6 +4817,17 @@ DefineStmt: /* can't use qualified_name, sigh */ n->typevar = makeRangeVarFromAnyName($3, @3, yyscanner); n->coldeflist = $6; + n->if_not_exists = false; + $$ = (Node *)n; + } + | CREATE TYPE_P IF_P NOT EXISTS any_name AS '(' OptTableFuncElementList ')' + { + CompositeTypeStmt *n = makeNode(CompositeTypeStmt); + + /* can't use qualified_name, sigh */ + n->typevar = makeRangeVarFromAnyName($6, @6, yyscanner); + n->coldeflist = $9; + n->if_not_exists = true; $$ = (Node *)n; } | CREATE TYPE_P any_name AS ENUM_P '(' opt_enum_val_list ')' @@ -4715,6 +4835,15 @@ DefineStmt: CreateEnumStmt *n = makeNode(CreateEnumStmt); n->typeName = $3; n->vals = $7; + n->if_not_exists = false; + $$ = (Node *)n; + } + | CREATE TYPE_P IF_P NOT EXISTS any_name AS ENUM_P '(' opt_enum_val_list ')' + { + CreateEnumStmt *n = makeNode(CreateEnumStmt); + n->typeName = $6; + n->vals = $10; + n->if_not_exists = true; $$ = (Node *)n; } | CREATE TYPE_P any_name AS RANGE definition @@ -4722,6 +4851,15 @@ DefineStmt: CreateRangeStmt *n = makeNode(CreateRangeStmt); n->typeName = $3; n->params = $6; + n->if_not_exists = false; + $$ = (Node *)n; + } + | CREATE TYPE_P IF_P NOT EXISTS any_name AS RANGE definition + { + CreateRangeStmt *n = makeNode(CreateRangeStmt); + n->typeName = $6; + n->params = $9; + n->if_not_exists = true; $$ = (Node *)n; } | CREATE TEXT_P SEARCH PARSER any_name definition @@ -4731,6 +4869,17 @@ DefineStmt: n->args = NIL; n->defnames = $5; n->definition = $6; + n->if_not_exists = false; + $$ = (Node *)n; + } + | CREATE TEXT_P SEARCH PARSER IF_P NOT EXISTS any_name definition + { + DefineStmt *n = makeNode(DefineStmt); + n->kind = OBJECT_TSPARSER; + n->args = NIL; + n->defnames = $8; + n->definition = $9; + n->if_not_exists = true; $$ = (Node *)n; } | CREATE TEXT_P SEARCH DICTIONARY any_name definition @@ -4740,6 +4889,17 @@ DefineStmt: n->args = NIL; n->defnames = $5; n->definition = $6; + n->if_not_exists = false; + $$ = (Node *)n; + } + | CREATE TEXT_P SEARCH DICTIONARY IF_P NOT EXISTS any_name definition + { + DefineStmt *n = makeNode(DefineStmt); + n->kind = OBJECT_TSDICTIONARY; + n->args = NIL; + n->defnames = $8; + n->definition = $9; + n->if_not_exists = true; $$ = (Node *)n; } | CREATE TEXT_P SEARCH TEMPLATE any_name definition @@ -4749,6 +4909,17 @@ DefineStmt: n->args = NIL; n->defnames = $5; n->definition = $6; + n->if_not_exists = false; + $$ = (Node *)n; + } + | CREATE TEXT_P SEARCH TEMPLATE IF_P NOT EXISTS any_name definition + { + DefineStmt *n = makeNode(DefineStmt); + n->kind = OBJECT_TSTEMPLATE; + n->args = NIL; + n->defnames = $8; + n->definition = $9; + n->if_not_exists = true; $$ = (Node *)n; } | CREATE TEXT_P SEARCH CONFIGURATION any_name definition @@ -4758,6 +4929,17 @@ DefineStmt: n->args = NIL; n->defnames = $5; n->definition = $6; + n->if_not_exists = false; + $$ = (Node *)n; + } + | CREATE TEXT_P SEARCH CONFIGURATION IF_P NOT EXISTS any_name definition + { + DefineStmt *n = makeNode(DefineStmt); + n->kind = OBJECT_TSCONFIGURATION; + n->args = NIL; + n->defnames = $8; + n->definition = $9; + n->if_not_exists = true; $$ = (Node *)n; } | CREATE COLLATION any_name definition @@ -4767,6 +4949,17 @@ DefineStmt: n->args = NIL; n->defnames = $3; n->definition = $4; + n->if_not_exists = false; + $$ = (Node *)n; + } + | CREATE COLLATION IF_P NOT EXISTS any_name definition + { + DefineStmt *n = makeNode(DefineStmt); + n->kind = OBJECT_COLLATION; + n->args = NIL; + n->defnames = $6; + n->definition = $7; + n->if_not_exists = true; $$ = (Node *)n; } | CREATE COLLATION any_name FROM any_name @@ -4776,6 +4969,17 @@ DefineStmt: n->args = NIL; n->defnames = $3; n->definition = list_make1(makeDefElem("from", (Node *) $5)); + n->if_not_exists = false; + $$ = (Node *)n; + } + | CREATE COLLATION IF_P NOT EXISTS any_name FROM any_name + { + DefineStmt *n = makeNode(DefineStmt); + n->kind = OBJECT_COLLATION; + n->args = NIL; + n->defnames = $6; + n->definition = list_make1(makeDefElem("from", (Node *) $8)); + n->if_not_exists = true; $$ = (Node *)n; } ; @@ -6147,6 +6351,7 @@ IndexStmt: CREATE opt_unique INDEX opt_concurrently opt_index_name n->isconstraint = false; n->deferrable = false; n->initdeferred = false; + n->if_not_exists = false; $$ = (Node *)n; } ; @@ -6764,37 +6969,40 @@ dostmt_opt_item: * *****************************************************************************/ -CreateCastStmt: CREATE CAST '(' Typename AS Typename ')' +CreateCastStmt: CREATE CAST opt_if_not_exists '(' Typename AS Typename ')' WITH FUNCTION function_with_argtypes cast_context { CreateCastStmt *n = makeNode(CreateCastStmt); - n->sourcetype = $4; - n->targettype = $6; - n->func = $10; - n->context = (CoercionContext) $11; + n->sourcetype = $5; + n->targettype = $7; + n->func = $11; + n->context = (CoercionContext) $12; n->inout = false; + n->if_not_exists = $3; $$ = (Node *)n; } - | CREATE CAST '(' Typename AS Typename ')' + | CREATE CAST opt_if_not_exists '(' Typename AS Typename ')' WITHOUT FUNCTION cast_context { CreateCastStmt *n = makeNode(CreateCastStmt); - n->sourcetype = $4; - n->targettype = $6; + n->sourcetype = $5; + n->targettype = $7; n->func = NULL; - n->context = (CoercionContext) $10; + n->context = (CoercionContext) $11; n->inout = false; + n->if_not_exists = $3; $$ = (Node *)n; } - | CREATE CAST '(' Typename AS Typename ')' + | CREATE CAST opt_if_not_exists '(' Typename AS Typename ')' WITH INOUT cast_context { CreateCastStmt *n = makeNode(CreateCastStmt); - n->sourcetype = $4; - n->targettype = $6; + n->sourcetype = $5; + n->targettype = $7; n->func = NULL; - n->context = (CoercionContext) $10; + n->context = (CoercionContext) $11; n->inout = true; + n->if_not_exists = $3; $$ = (Node *)n; } ; @@ -8315,10 +8523,21 @@ CreateDomainStmt: CreateDomainStmt *n = makeNode(CreateDomainStmt); n->domainname = $3; n->typeName = $5; + n->if_not_exists = false; SplitColQualList($6, &n->constraints, &n->collClause, yyscanner); $$ = (Node *)n; } + | CREATE DOMAIN_P IF_P NOT EXISTS any_name opt_as Typename ColQualList + { + CreateDomainStmt *n = makeNode(CreateDomainStmt); + n->domainname = $6; + n->typeName = $8; + n->if_not_exists = true; + SplitColQualList($9, &n->constraints, &n->collClause, + yyscanner); + $$ = (Node *)n; + } ; AlterDomainStmt: diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.c index 6a7bf0d..6324107 100644 --- a/src/backend/tcop/utility.c +++ b/src/backend/tcop/utility.c @@ -1105,34 +1105,40 @@ ProcessUtilitySlow(Node *parsetree, case OBJECT_AGGREGATE: DefineAggregate(stmt->defnames, stmt->args, stmt->oldstyle, stmt->definition, - queryString); + queryString, stmt->if_not_exists); break; case OBJECT_OPERATOR: Assert(stmt->args == NIL); - DefineOperator(stmt->defnames, stmt->definition); + DefineOperator(stmt->defnames, stmt->definition, + stmt->if_not_exists); break; case OBJECT_TYPE: Assert(stmt->args == NIL); - DefineType(stmt->defnames, stmt->definition); + DefineType(stmt->defnames, stmt->definition, + stmt->if_not_exists); break; case OBJECT_TSPARSER: Assert(stmt->args == NIL); - DefineTSParser(stmt->defnames, stmt->definition); + DefineTSParser(stmt->defnames, stmt->definition, + stmt->if_not_exists); break; case OBJECT_TSDICTIONARY: Assert(stmt->args == NIL); DefineTSDictionary(stmt->defnames, - stmt->definition); + stmt->definition, + stmt->if_not_exists); break; case OBJECT_TSTEMPLATE: Assert(stmt->args == NIL); DefineTSTemplate(stmt->defnames, - stmt->definition); + stmt->definition, + stmt->if_not_exists); break; case OBJECT_TSCONFIGURATION: Assert(stmt->args == NIL); DefineTSConfiguration(stmt->defnames, - stmt->definition); + stmt->definition, + stmt->if_not_exists); break; case OBJECT_COLLATION: Assert(stmt->args == NIL); @@ -1213,7 +1219,7 @@ ProcessUtilitySlow(Node *parsetree, { CompositeTypeStmt *stmt = (CompositeTypeStmt *) parsetree; - DefineCompositeType(stmt->typevar, stmt->coldeflist); + DefineCompositeType(stmt->typevar, stmt->coldeflist, stmt->if_not_exists); } break; diff --git a/src/include/catalog/heap.h b/src/include/catalog/heap.h index b43765b..c6ef47c 100644 --- a/src/include/catalog/heap.h +++ b/src/include/catalog/heap.h @@ -68,7 +68,8 @@ extern Oid heap_create_with_catalog(const char *relname, Datum reloptions, bool use_user_acl, bool allow_system_table_mods, - bool is_internal); + bool is_internal, + bool ifNotExists); extern void heap_create_init_fork(Relation rel); diff --git a/src/include/catalog/index.h b/src/include/catalog/index.h index e697275..ffeddfe 100644 --- a/src/include/catalog/index.h +++ b/src/include/catalog/index.h @@ -60,7 +60,8 @@ extern Oid index_create(Relation heapRelation, bool allow_system_table_mods, bool skip_build, bool concurrent, - bool is_internal); + bool is_internal, + bool ifNotExists); extern void index_constraint_create(Relation heapRelation, Oid indexRelationId, diff --git a/src/include/catalog/pg_aggregate.h b/src/include/catalog/pg_aggregate.h index 034456a..435a75d 100644 --- a/src/include/catalog/pg_aggregate.h +++ b/src/include/catalog/pg_aggregate.h @@ -254,6 +254,7 @@ extern Oid AggregateCreate(const char *aggName, List *aggsortopName, Oid aggTransType, int32 aggTransSpace, - const char *agginitval); + const char *agginitval, + bool aggIfNotExists); #endif /* PG_AGGREGATE_H */ diff --git a/src/include/catalog/pg_operator.h b/src/include/catalog/pg_operator.h index 0350ef6..1eef96c 100644 --- a/src/include/catalog/pg_operator.h +++ b/src/include/catalog/pg_operator.h @@ -1766,6 +1766,7 @@ extern Oid OperatorCreate(const char *operatorName, Oid restrictionId, Oid joinId, bool canMerge, - bool canHash); + bool canHash, + bool ifNotExists); #endif /* PG_OPERATOR_H */ diff --git a/src/include/catalog/pg_proc_fn.h b/src/include/catalog/pg_proc_fn.h index 3b04301..d920c0b 100644 --- a/src/include/catalog/pg_proc_fn.h +++ b/src/include/catalog/pg_proc_fn.h @@ -39,7 +39,8 @@ extern Oid ProcedureCreate(const char *procedureName, List *parameterDefaults, Datum proconfig, float4 procost, - float4 prorows); + float4 prorows, + bool ifNotExists); extern bool function_parse_error_transpose(const char *prosrc); diff --git a/src/include/catalog/pg_type_fn.h b/src/include/catalog/pg_type_fn.h index b12d58a..e21a817 100644 --- a/src/include/catalog/pg_type_fn.h +++ b/src/include/catalog/pg_type_fn.h @@ -51,7 +51,8 @@ extern Oid TypeCreate(Oid newTypeOid, int32 typeMod, int32 typNDims, bool typeNotNull, - Oid typeCollation); + Oid typeCollation, + bool ifNotExists); extern void GenerateTypeDependencies(Oid typeNamespace, Oid typeObjectId, diff --git a/src/include/commands/defrem.h b/src/include/commands/defrem.h index f8ceb5d..5bcfa4b 100644 --- a/src/include/commands/defrem.h +++ b/src/include/commands/defrem.h @@ -66,12 +66,12 @@ extern void interpret_function_parameter_list(List *parameters, Oid *requiredResultType); /* commands/operatorcmds.c */ -extern Oid DefineOperator(List *names, List *parameters); +extern Oid DefineOperator(List *names, List *parameters, bool ifNotExists); extern void RemoveOperatorById(Oid operOid); /* commands/aggregatecmds.c */ extern Oid DefineAggregate(List *name, List *args, bool oldstyle, - List *parameters, const char *queryString); + List *parameters, const char *queryString, bool ifNotExists); /* commands/opclasscmds.c */ extern Oid DefineOpClass(CreateOpClassStmt *stmt); @@ -90,17 +90,17 @@ extern Oid get_opclass_oid(Oid amID, List *opclassname, bool missing_ok); extern Oid get_opfamily_oid(Oid amID, List *opfamilyname, bool missing_ok); /* commands/tsearchcmds.c */ -extern Oid DefineTSParser(List *names, List *parameters); +extern Oid DefineTSParser(List *names, List *parameters, bool ifNotExists); extern void RemoveTSParserById(Oid prsId); -extern Oid DefineTSDictionary(List *names, List *parameters); +extern Oid DefineTSDictionary(List *names, List *parameters, bool ifNotExists); extern void RemoveTSDictionaryById(Oid dictId); extern Oid AlterTSDictionary(AlterTSDictionaryStmt *stmt); -extern Oid DefineTSTemplate(List *names, List *parameters); +extern Oid DefineTSTemplate(List *names, List *parameters, bool ifNotExists); extern void RemoveTSTemplateById(Oid tmplId); -extern Oid DefineTSConfiguration(List *names, List *parameters); +extern Oid DefineTSConfiguration(List *names, List *parameters, bool ifNotExists); extern void RemoveTSConfigurationById(Oid cfgId); extern Oid AlterTSConfiguration(AlterTSConfigurationStmt *stmt); diff --git a/src/include/commands/typecmds.h b/src/include/commands/typecmds.h index f45fde7..dbd4e32 100644 --- a/src/include/commands/typecmds.h +++ b/src/include/commands/typecmds.h @@ -21,13 +21,13 @@ #define DEFAULT_TYPDELIM ',' -extern Oid DefineType(List *names, List *parameters); +extern Oid DefineType(List *names, List *parameters, bool ifNotExists); extern void RemoveTypeById(Oid typeOid); extern Oid DefineDomain(CreateDomainStmt *stmt); extern Oid DefineEnum(CreateEnumStmt *stmt); extern Oid DefineRange(CreateRangeStmt *stmt); extern Oid AlterEnum(AlterEnumStmt *stmt, bool isTopLevel); -extern Oid DefineCompositeType(RangeVar *typevar, List *coldeflist); +extern Oid DefineCompositeType(RangeVar *typevar, List *coldeflist, bool ifNotExists); extern Oid AssignTypeArrayOid(void); extern Oid AlterDomainDefault(List *names, Node *defaultRaw); diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index 55524b4..480c7cf 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -1808,6 +1808,7 @@ typedef struct CreateEventTrigStmt char *eventname; /* event's identifier */ List *whenclause; /* list of DefElems indicating filtering */ List *funcname; /* qual. name of function to call */ + bool if_not_exists; /* just do nothing if the event trigger already exists? */ } CreateEventTrigStmt; /* ---------------------- @@ -1860,6 +1861,7 @@ typedef struct CreateRoleStmt RoleStmtType stmt_type; /* ROLE/USER/GROUP */ char *role; /* role name */ List *options; /* List of DefElem nodes */ + bool if_not_exists; /* skip error if a Role already exists */ } CreateRoleStmt; typedef struct AlterRoleStmt @@ -1896,6 +1898,7 @@ typedef struct CreateSeqStmt RangeVar *sequence; /* the sequence to create */ List *options; Oid ownerId; /* ID of owner, or InvalidOid for default */ + bool if_not_exists; /* skip error if a Sequence already exists */ } CreateSeqStmt; typedef struct AlterSeqStmt @@ -1918,6 +1921,7 @@ typedef struct DefineStmt List *defnames; /* qualified name (list of Value strings) */ List *args; /* a list of TypeName (if needed) */ List *definition; /* a list of DefElem */ + bool if_not_exists; /* just do nothing if {aggregate|operator|type} already exists? */ } DefineStmt; /* ---------------------- @@ -1931,6 +1935,7 @@ typedef struct CreateDomainStmt TypeName *typeName; /* the base type */ CollateClause *collClause; /* untransformed COLLATE spec, if any */ List *constraints; /* constraints (list of Constraint nodes) */ + bool if_not_exists; /* just do nothing if domain already exists? */ } CreateDomainStmt; /* ---------------------- @@ -2138,6 +2143,7 @@ typedef struct IndexStmt bool deferrable; /* is the constraint DEFERRABLE? */ bool initdeferred; /* is the constraint INITIALLY DEFERRED? */ bool concurrent; /* should this be a concurrent index build? */ + bool if_not_exists; /* just do nothing if index already exists? */ } IndexStmt; /* ---------------------- @@ -2332,6 +2338,7 @@ typedef struct CompositeTypeStmt NodeTag type; RangeVar *typevar; /* the composite type to be created */ List *coldeflist; /* list of ColumnDef nodes */ + bool if_not_exists; /* just do nothing if type already exists? */ } CompositeTypeStmt; /* ---------------------- @@ -2343,6 +2350,7 @@ typedef struct CreateEnumStmt NodeTag type; List *typeName; /* qualified name (list of Value strings) */ List *vals; /* enum values (list of Value strings) */ + bool if_not_exists; /* just do nothing if type already exists? */ } CreateEnumStmt; /* ---------------------- @@ -2354,6 +2362,7 @@ typedef struct CreateRangeStmt NodeTag type; List *typeName; /* qualified name (list of Value strings) */ List *params; /* range parameters (list of DefElem) */ + bool if_not_exists; /* just do nothing if type already exists? */ } CreateRangeStmt; /* ---------------------- @@ -2624,6 +2633,7 @@ typedef struct CreateCastStmt FuncWithArgs *func; CoercionContext context; bool inout; + bool if_not_exists; /* just do nothing if cast already exists? */ } CreateCastStmt; /* ---------------------- diff --git a/src/test/regress/expected/alter_generic.out b/src/test/regress/expected/alter_generic.out index 7845b8a..dfb0a7a 100644 --- a/src/test/regress/expected/alter_generic.out +++ b/src/test/regress/expected/alter_generic.out @@ -581,6 +581,10 @@ SELECT nspname, cfgname, rolname -- Text Search Template -- CREATE TEXT SEARCH TEMPLATE alt_ts_temp1 (lexize=dsimple_lexize); +CREATE TEXT SEARCH TEMPLATE alt_ts_temp1 (lexize=dsimple_lexize); +ERROR: text search template "alt_nsp1"."alt_ts_temp1" already exists +CREATE TEXT SEARCH TEMPLATE IF NOT EXISTS alt_ts_temp1 (lexize=dsimple_lexize); +NOTICE: text search template "alt_nsp1"."alt_ts_temp1" already exists, skipping CREATE TEXT SEARCH TEMPLATE alt_ts_temp2 (lexize=dsimple_lexize); ALTER TEXT SEARCH TEMPLATE alt_ts_temp1 RENAME TO alt_ts_temp2; -- failed (name conflict) ERROR: text search template "alt_ts_temp2" already exists in schema "alt_nsp1" @@ -605,6 +609,12 @@ SELECT nspname, tmplname -- CREATE TEXT SEARCH PARSER alt_ts_prs1 (start = prsd_start, gettoken = prsd_nexttoken, end = prsd_end, lextypes = prsd_lextype); +CREATE TEXT SEARCH PARSER alt_ts_prs1 + (start = prsd_start, gettoken = prsd_nexttoken, end = prsd_end, lextypes = prsd_lextype); +ERROR: text search parser "alt_nsp1"."alt_ts_prs1" already exists +CREATE TEXT SEARCH PARSER IF NOT EXISTS alt_ts_prs1 + (start = prsd_start, gettoken = prsd_nexttoken, end = prsd_end, lextypes = prsd_lextype); +NOTICE: text search parser "alt_nsp1"."alt_ts_prs1" already exists, skipping CREATE TEXT SEARCH PARSER alt_ts_prs2 (start = prsd_start, gettoken = prsd_nexttoken, end = prsd_end, lextypes = prsd_lextype); ALTER TEXT SEARCH PARSER alt_ts_prs1 RENAME TO alt_ts_prs2; -- failed (name conflict) diff --git a/src/test/regress/expected/create_aggregate.out b/src/test/regress/expected/create_aggregate.out index 9ecaea1..eda1966 100644 --- a/src/test/regress/expected/create_aggregate.out +++ b/src/test/regress/expected/create_aggregate.out @@ -12,6 +12,19 @@ COMMENT ON AGGREGATE newavg_wrong (int4) IS 'an agg comment'; ERROR: aggregate newavg_wrong(integer) does not exist COMMENT ON AGGREGATE newavg (int4) IS 'an agg comment'; COMMENT ON AGGREGATE newavg (int4) IS NULL; +-- test IF NOT EXISTS +CREATE AGGREGATE newavg ( + sfunc = int4_avg_accum, basetype = int4, stype = _int8, + finalfunc = int8_avg, + initcond1 = '{0,0}' +); +ERROR: function "newavg" already exists with same argument types +CREATE AGGREGATE IF NOT EXISTS newavg ( + sfunc = int4_avg_accum, basetype = int4, stype = _int8, + finalfunc = int8_avg, + initcond1 = '{0,0}' +); +NOTICE: function "newavg" already exists with same argument types, skipping -- without finalfunc; test obsolete spellings 'sfunc1' etc CREATE AGGREGATE newsum ( sfunc1 = int4pl, basetype = int4, stype1 = int4, diff --git a/src/test/regress/expected/create_cast.out b/src/test/regress/expected/create_cast.out index 56cd86e..1c3e6f0 100644 --- a/src/test/regress/expected/create_cast.out +++ b/src/test/regress/expected/create_cast.out @@ -29,6 +29,10 @@ LINE 1: SELECT casttestfunc('foo'::text); HINT: No function matches the given name and argument types. You might need to add explicit type casts. -- Try binary coercion cast CREATE CAST (text AS casttesttype) WITHOUT FUNCTION; +CREATE CAST (text AS casttesttype) WITHOUT FUNCTION; +ERROR: cast from type text to type casttesttype already exists +CREATE CAST IF NOT EXISTS (text AS casttesttype) WITHOUT FUNCTION; +NOTICE: cast from type text to type casttesttype already exists, skipping SELECT casttestfunc('foo'::text); -- doesn't work, as the cast is explicit ERROR: function casttestfunc(text) does not exist LINE 1: SELECT casttestfunc('foo'::text); @@ -43,6 +47,10 @@ SELECT casttestfunc('foo'::text::casttesttype); -- should work DROP CAST (text AS casttesttype); -- cleanup -- Try IMPLICIT binary coercion cast CREATE CAST (text AS casttesttype) WITHOUT FUNCTION AS IMPLICIT; +CREATE CAST (text AS casttesttype) WITHOUT FUNCTION AS IMPLICIT; +ERROR: cast from type text to type casttesttype already exists +CREATE CAST IF NOT EXISTS (text AS casttesttype) WITHOUT FUNCTION AS IMPLICIT; +NOTICE: cast from type text to type casttesttype already exists, skipping SELECT casttestfunc('foo'::text); -- Should work now casttestfunc -------------- @@ -55,6 +63,10 @@ ERROR: cannot cast type integer to casttesttype LINE 1: SELECT 1234::int4::casttesttype; ^ CREATE CAST (int4 AS casttesttype) WITH INOUT; +CREATE CAST (int4 AS casttesttype) WITH INOUT; +ERROR: cast from type integer to type casttesttype already exists +CREATE CAST IF NOT EXISTS (int4 AS casttesttype) WITH INOUT; +NOTICE: cast from type integer to type casttesttype already exists, skipping SELECT 1234::int4::casttesttype; -- Should work now casttesttype -------------- @@ -66,6 +78,10 @@ DROP CAST (int4 AS casttesttype); CREATE FUNCTION int4_casttesttype(int4) RETURNS casttesttype LANGUAGE SQL AS $$ SELECT ('foo'::text || $1::text)::casttesttype; $$; CREATE CAST (int4 AS casttesttype) WITH FUNCTION int4_casttesttype(int4) AS IMPLICIT; +CREATE CAST (int4 AS casttesttype) WITH FUNCTION int4_casttesttype(int4) AS IMPLICIT; +ERROR: cast from type integer to type casttesttype already exists +CREATE CAST IF NOT EXISTS (int4 AS casttesttype) WITH FUNCTION int4_casttesttype(int4) AS IMPLICIT; +NOTICE: cast from type integer to type casttesttype already exists, skipping SELECT 1234::int4::casttesttype; -- Should work now casttesttype -------------- diff --git a/src/test/regress/expected/create_operator.out b/src/test/regress/expected/create_operator.out index 2e6c764..3e5a2f7 100644 --- a/src/test/regress/expected/create_operator.out +++ b/src/test/regress/expected/create_operator.out @@ -7,6 +7,20 @@ CREATE OPERATOR ## ( procedure = path_inter, commutator = ## ); +CREATE OPERATOR ## ( + leftarg = path, + rightarg = path, + procedure = path_inter, + commutator = ## +); +ERROR: operator ## already exists +CREATE OPERATOR IF NOT EXISTS ## ( + leftarg = path, + rightarg = path, + procedure = path_inter, + commutator = ## +); +NOTICE: operator ## already exists, skipping CREATE OPERATOR <% ( leftarg = point, rightarg = widget, diff --git a/src/test/regress/expected/create_type.out b/src/test/regress/expected/create_type.out index 6dfe916..666a7c1 100644 --- a/src/test/regress/expected/create_type.out +++ b/src/test/regress/expected/create_type.out @@ -14,6 +14,24 @@ CREATE TYPE widget ( typmod_out = numerictypmodout, alignment = double ); +CREATE TYPE widget ( + internallength = 24, + input = widget_in, + output = widget_out, + typmod_in = numerictypmodin, + typmod_out = numerictypmodout, + alignment = double +); +ERROR: type "widget" already exists +CREATE TYPE IF NOT EXISTS widget ( + internallength = 24, + input = widget_in, + output = widget_out, + typmod_in = numerictypmodin, + typmod_out = numerictypmodout, + alignment = double +); +NOTICE: type "widget" already exists, skipping CREATE TYPE city_budget ( internallength = 16, input = int44in, @@ -26,6 +44,8 @@ CREATE TYPE city_budget ( CREATE TYPE shell; CREATE TYPE shell; -- fail, type already present ERROR: type "shell" already exists +CREATE TYPE IF NOT EXISTS shell; -- do not fail, just skip +NOTICE: type "shell" already exists, skipping DROP TYPE shell; DROP TYPE shell; -- fail, type not exist ERROR: type "shell" does not exist @@ -83,6 +103,10 @@ SELECT * FROM default_test; -- Test stand-alone composite type CREATE TYPE default_test_row AS (f1 text_w_default, f2 int42); +CREATE TYPE default_test_row AS (f1 text_w_default, f2 int42); +ERROR: type "default_test_row" already exists +CREATE TYPE IF NOT EXISTS default_test_row AS (f1 text_w_default, f2 int42); +NOTICE: type "default_test_row" already exists, skipping CREATE FUNCTION get_default_test() RETURNS SETOF default_test_row AS ' SELECT * FROM default_test; ' LANGUAGE SQL; diff --git a/src/test/regress/expected/domain.out b/src/test/regress/expected/domain.out index 78e7704..3a03df8 100644 --- a/src/test/regress/expected/domain.out +++ b/src/test/regress/expected/domain.out @@ -1,6 +1,12 @@ -- -- Test domains. -- +-- Test IF NOT EXISTS +create domain domainifnotexists int4; +create domain domainifnotexists int4; +ERROR: type "domainifnotexists" already exists +create domain if not exists domainifnotexists int4; +NOTICE: type "domainifnotexists" already exists, skipping -- Test Comment / Drop create domain domaindroptest int4; comment on domain domaindroptest is 'About to drop this..'; diff --git a/src/test/regress/expected/enum.out b/src/test/regress/expected/enum.out index 3682642..b95e6a5 100644 --- a/src/test/regress/expected/enum.out +++ b/src/test/regress/expected/enum.out @@ -2,6 +2,10 @@ -- Enum tests -- CREATE TYPE rainbow AS ENUM ('red', 'orange', 'yellow', 'green', 'blue', 'purple'); +CREATE TYPE rainbow AS ENUM ('red', 'orange', 'yellow', 'green', 'blue', 'purple'); +ERROR: type "rainbow" already exists +CREATE TYPE IF NOT EXISTS rainbow AS ENUM ('red', 'orange', 'yellow', 'green', 'blue', 'purple'); +NOTICE: type "rainbow" already exists, skipping -- -- Did it create the right number of rows? -- diff --git a/src/test/regress/expected/event_trigger.out b/src/test/regress/expected/event_trigger.out index 656d47f..b3a6ad8 100644 --- a/src/test/regress/expected/event_trigger.out +++ b/src/test/regress/expected/event_trigger.out @@ -16,6 +16,14 @@ ERROR: unrecognized event name "elephant_bootstrap" -- OK create event trigger regress_event_trigger on ddl_command_start execute procedure test_event_trigger(); +-- FAIL +create event trigger regress_event_trigger on ddl_command_start + execute procedure test_event_trigger(); +ERROR: event trigger "regress_event_trigger" already exists +-- FAIL, but skipp +create event trigger if not exists regress_event_trigger on ddl_command_start + execute procedure test_event_trigger(); +NOTICE: event trigger "regress_event_trigger" already exists, skipping -- OK create event trigger regress_event_trigger_end on ddl_command_end execute procedure test_event_trigger(); diff --git a/src/test/regress/expected/rangetypes.out b/src/test/regress/expected/rangetypes.out index 39db992..7397498 100644 --- a/src/test/regress/expected/rangetypes.out +++ b/src/test/regress/expected/rangetypes.out @@ -1,5 +1,9 @@ -- Tests for range data types. create type textrange as range (subtype=text, collation="C"); +create type textrange as range (subtype=text, collation="C"); +ERROR: type "textrange" already exists +create type if not exists textrange as range (subtype=text, collation="C"); +NOTICE: type "textrange" already exists, skipping -- -- test input parser -- diff --git a/src/test/regress/expected/roles.out b/src/test/regress/expected/roles.out new file mode 100644 index 0000000..267bf53 --- /dev/null +++ b/src/test/regress/expected/roles.out @@ -0,0 +1,22 @@ +CREATE ROLE test_nonsuperuser; +CREATE ROLE test_superuser SUPERUSER; +SELECT * FROM pg_authid WHERE rolname ~ '^test_'; + rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcatupdate | rolcanlogin | rolreplication | rolconnlimit | rolpassword | rolvaliduntil +-------------------+----------+------------+---------------+-------------+--------------+-------------+----------------+--------------+-------------+--------------- + test_nonsuperuser | f | t | f | f | f | f | f | -1 | | + test_superuser | t | t | f | f | t | f | f | -1 | | +(2 rows) + +CREATE ROLE test_nonsuperuser; +ERROR: role "test_nonsuperuser" already exists +CREATE ROLE IF NOT EXISTS test_nonsuperuser; +NOTICE: role "test_nonsuperuser" already exists, skipping +ALTER ROLE test_nonsuperuser LOGIN PASSWORD 'pass'; +ALTER ROLE test_superuser LOGIN PASSWORD 'pass'; +SELECT * FROM pg_authid WHERE rolname ~ '^test_'; + rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcatupdate | rolcanlogin | rolreplication | rolconnlimit | rolpassword | rolvaliduntil +-------------------+----------+------------+---------------+-------------+--------------+-------------+----------------+--------------+-------------------------------------+--------------- + test_nonsuperuser | f | t | f | f | f | t | f | -1 | md599efb740a92ca6fdbf8f43199c5d6d62 | + test_superuser | t | t | f | f | t | t | f | -1 | md50d734aa7bd80d7c412764cf687848452 | +(2 rows) + diff --git a/src/test/regress/expected/sequence.out b/src/test/regress/expected/sequence.out index 8fcb700..a27b5fd 100644 --- a/src/test/regress/expected/sequence.out +++ b/src/test/regress/expected/sequence.out @@ -91,6 +91,8 @@ SELECT nextval('serialTest2_f6_seq'); -- basic sequence operations using both text and oid references CREATE SEQUENCE sequence_test; +CREATE SEQUENCE IF NOT EXISTS sequence_test; +NOTICE: relation "sequence_test" already exists, skipping SELECT nextval('sequence_test'::text); nextval --------- diff --git a/src/test/regress/expected/tsdicts.out b/src/test/regress/expected/tsdicts.out index 9df1434..3214609 100644 --- a/src/test/regress/expected/tsdicts.out +++ b/src/test/regress/expected/tsdicts.out @@ -5,6 +5,18 @@ CREATE TEXT SEARCH DICTIONARY ispell ( DictFile=ispell_sample, AffFile=ispell_sample ); +CREATE TEXT SEARCH DICTIONARY ispell ( + Template=ispell, + DictFile=ispell_sample, + AffFile=ispell_sample +); +ERROR: text search dictionary "public"."ispell" already exists +CREATE TEXT SEARCH DICTIONARY IF NOT EXISTS ispell ( + Template=ispell, + DictFile=ispell_sample, + AffFile=ispell_sample +); +NOTICE: text search dictionary "public"."ispell" already exists, skipping SELECT ts_lexize('ispell', 'skies'); ts_lexize ----------- @@ -232,6 +244,14 @@ SELECT ts_lexize('thesaurus', 'one'); CREATE TEXT SEARCH CONFIGURATION ispell_tst ( COPY=english ); +CREATE TEXT SEARCH CONFIGURATION ispell_tst ( + COPY=english +); +ERROR: text search configuration "public"."ispell_tst" already exists +CREATE TEXT SEARCH CONFIGURATION IF NOT EXISTS ispell_tst ( + COPY=english +); +NOTICE: text search configuration "public"."ispell_tst" already exists, skipping ALTER TEXT SEARCH CONFIGURATION ispell_tst ALTER MAPPING FOR word, numword, asciiword, hword, numhword, asciihword, hword_part, hword_numpart, hword_asciipart WITH ispell, english_stem; diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule index 5758b07..93c8a77 100644 --- a/src/test/regress/parallel_schedule +++ b/src/test/regress/parallel_schedule @@ -105,7 +105,7 @@ test: select_views portals_p2 foreign_key cluster dependency guc bitmapops combo # NB: temp.sql does a reconnect which transiently uses 2 connections, # so keep this parallel group to at most 19 tests # ---------- -test: plancache limit plpgsql copy2 temp domain rangefuncs prepare without_oid conversion truncate alter_table sequence polymorphism rowtypes returning largeobject with xml +test: plancache limit plpgsql copy2 temp domain rangefuncs prepare without_oid conversion truncate alter_table sequence polymorphism rowtypes returning largeobject with xml roles # run stats by itself because its delay may be insufficient under heavy load test: stats diff --git a/src/test/regress/serial_schedule b/src/test/regress/serial_schedule index 78348f5..3ee8018 100644 --- a/src/test/regress/serial_schedule +++ b/src/test/regress/serial_schedule @@ -142,3 +142,4 @@ test: largeobject test: with test: xml test: stats +test: roles diff --git a/src/test/regress/sql/alter_generic.sql b/src/test/regress/sql/alter_generic.sql index f46cbc8..1f148f3 100644 --- a/src/test/regress/sql/alter_generic.sql +++ b/src/test/regress/sql/alter_generic.sql @@ -501,6 +501,8 @@ SELECT nspname, cfgname, rolname -- Text Search Template -- CREATE TEXT SEARCH TEMPLATE alt_ts_temp1 (lexize=dsimple_lexize); +CREATE TEXT SEARCH TEMPLATE alt_ts_temp1 (lexize=dsimple_lexize); +CREATE TEXT SEARCH TEMPLATE IF NOT EXISTS alt_ts_temp1 (lexize=dsimple_lexize); CREATE TEXT SEARCH TEMPLATE alt_ts_temp2 (lexize=dsimple_lexize); ALTER TEXT SEARCH TEMPLATE alt_ts_temp1 RENAME TO alt_ts_temp2; -- failed (name conflict) @@ -521,6 +523,10 @@ SELECT nspname, tmplname CREATE TEXT SEARCH PARSER alt_ts_prs1 (start = prsd_start, gettoken = prsd_nexttoken, end = prsd_end, lextypes = prsd_lextype); +CREATE TEXT SEARCH PARSER alt_ts_prs1 + (start = prsd_start, gettoken = prsd_nexttoken, end = prsd_end, lextypes = prsd_lextype); +CREATE TEXT SEARCH PARSER IF NOT EXISTS alt_ts_prs1 + (start = prsd_start, gettoken = prsd_nexttoken, end = prsd_end, lextypes = prsd_lextype); CREATE TEXT SEARCH PARSER alt_ts_prs2 (start = prsd_start, gettoken = prsd_nexttoken, end = prsd_end, lextypes = prsd_lextype); diff --git a/src/test/regress/sql/create_aggregate.sql b/src/test/regress/sql/create_aggregate.sql index 7ea23de..4bba46a 100644 --- a/src/test/regress/sql/create_aggregate.sql +++ b/src/test/regress/sql/create_aggregate.sql @@ -14,6 +14,18 @@ COMMENT ON AGGREGATE newavg_wrong (int4) IS 'an agg comment'; COMMENT ON AGGREGATE newavg (int4) IS 'an agg comment'; COMMENT ON AGGREGATE newavg (int4) IS NULL; +-- test IF NOT EXISTS +CREATE AGGREGATE newavg ( + sfunc = int4_avg_accum, basetype = int4, stype = _int8, + finalfunc = int8_avg, + initcond1 = '{0,0}' +); +CREATE AGGREGATE IF NOT EXISTS newavg ( + sfunc = int4_avg_accum, basetype = int4, stype = _int8, + finalfunc = int8_avg, + initcond1 = '{0,0}' +); + -- without finalfunc; test obsolete spellings 'sfunc1' etc CREATE AGGREGATE newsum ( sfunc1 = int4pl, basetype = int4, stype1 = int4, diff --git a/src/test/regress/sql/create_cast.sql b/src/test/regress/sql/create_cast.sql index ad348da..ec9e266 100644 --- a/src/test/regress/sql/create_cast.sql +++ b/src/test/regress/sql/create_cast.sql @@ -29,18 +29,24 @@ SELECT casttestfunc('foo'::text); -- fails, as there's no cast -- Try binary coercion cast CREATE CAST (text AS casttesttype) WITHOUT FUNCTION; +CREATE CAST (text AS casttesttype) WITHOUT FUNCTION; +CREATE CAST IF NOT EXISTS (text AS casttesttype) WITHOUT FUNCTION; SELECT casttestfunc('foo'::text); -- doesn't work, as the cast is explicit SELECT casttestfunc('foo'::text::casttesttype); -- should work DROP CAST (text AS casttesttype); -- cleanup -- Try IMPLICIT binary coercion cast CREATE CAST (text AS casttesttype) WITHOUT FUNCTION AS IMPLICIT; +CREATE CAST (text AS casttesttype) WITHOUT FUNCTION AS IMPLICIT; +CREATE CAST IF NOT EXISTS (text AS casttesttype) WITHOUT FUNCTION AS IMPLICIT; SELECT casttestfunc('foo'::text); -- Should work now -- Try I/O conversion cast. SELECT 1234::int4::casttesttype; -- No cast yet, should fail CREATE CAST (int4 AS casttesttype) WITH INOUT; +CREATE CAST (int4 AS casttesttype) WITH INOUT; +CREATE CAST IF NOT EXISTS (int4 AS casttesttype) WITH INOUT; SELECT 1234::int4::casttesttype; -- Should work now DROP CAST (int4 AS casttesttype); @@ -51,4 +57,6 @@ CREATE FUNCTION int4_casttesttype(int4) RETURNS casttesttype LANGUAGE SQL AS $$ SELECT ('foo'::text || $1::text)::casttesttype; $$; CREATE CAST (int4 AS casttesttype) WITH FUNCTION int4_casttesttype(int4) AS IMPLICIT; +CREATE CAST (int4 AS casttesttype) WITH FUNCTION int4_casttesttype(int4) AS IMPLICIT; +CREATE CAST IF NOT EXISTS (int4 AS casttesttype) WITH FUNCTION int4_casttesttype(int4) AS IMPLICIT; SELECT 1234::int4::casttesttype; -- Should work now diff --git a/src/test/regress/sql/create_operator.sql b/src/test/regress/sql/create_operator.sql index f7a372a..8278f88 100644 --- a/src/test/regress/sql/create_operator.sql +++ b/src/test/regress/sql/create_operator.sql @@ -1,14 +1,24 @@ -- -- CREATE_OPERATOR -- - CREATE OPERATOR ## ( leftarg = path, rightarg = path, procedure = path_inter, commutator = ## ); - +CREATE OPERATOR ## ( + leftarg = path, + rightarg = path, + procedure = path_inter, + commutator = ## +); +CREATE OPERATOR IF NOT EXISTS ## ( + leftarg = path, + rightarg = path, + procedure = path_inter, + commutator = ## +); CREATE OPERATOR <% ( leftarg = point, rightarg = widget, @@ -16,22 +26,18 @@ CREATE OPERATOR <% ( commutator = >% , negator = >=% ); - CREATE OPERATOR @#@ ( rightarg = int8, -- left unary procedure = numeric_fac ); - CREATE OPERATOR #@# ( leftarg = int8, -- right unary procedure = numeric_fac ); - CREATE OPERATOR #%# ( leftarg = int8, -- right unary procedure = numeric_fac ); - -- Test comments COMMENT ON OPERATOR ###### (int4, NONE) IS 'bad right unary'; diff --git a/src/test/regress/sql/create_type.sql b/src/test/regress/sql/create_type.sql index a4906b6..79e0181 100644 --- a/src/test/regress/sql/create_type.sql +++ b/src/test/regress/sql/create_type.sql @@ -16,6 +16,24 @@ CREATE TYPE widget ( alignment = double ); +CREATE TYPE widget ( + internallength = 24, + input = widget_in, + output = widget_out, + typmod_in = numerictypmodin, + typmod_out = numerictypmodout, + alignment = double +); + +CREATE TYPE IF NOT EXISTS widget ( + internallength = 24, + input = widget_in, + output = widget_out, + typmod_in = numerictypmodin, + typmod_out = numerictypmodout, + alignment = double +); + CREATE TYPE city_budget ( internallength = 16, input = int44in, @@ -28,6 +46,7 @@ CREATE TYPE city_budget ( -- Test creation and destruction of shell types CREATE TYPE shell; CREATE TYPE shell; -- fail, type already present +CREATE TYPE IF NOT EXISTS shell; -- do not fail, just skip DROP TYPE shell; DROP TYPE shell; -- fail, type not exist @@ -85,6 +104,10 @@ SELECT * FROM default_test; CREATE TYPE default_test_row AS (f1 text_w_default, f2 int42); +CREATE TYPE default_test_row AS (f1 text_w_default, f2 int42); + +CREATE TYPE IF NOT EXISTS default_test_row AS (f1 text_w_default, f2 int42); + CREATE FUNCTION get_default_test() RETURNS SETOF default_test_row AS ' SELECT * FROM default_test; ' LANGUAGE SQL; diff --git a/src/test/regress/sql/domain.sql b/src/test/regress/sql/domain.sql index 5af36af..f44f04c 100644 --- a/src/test/regress/sql/domain.sql +++ b/src/test/regress/sql/domain.sql @@ -2,6 +2,11 @@ -- Test domains. -- +-- Test IF NOT EXISTS +create domain domainifnotexists int4; +create domain domainifnotexists int4; +create domain if not exists domainifnotexists int4; + -- Test Comment / Drop create domain domaindroptest int4; comment on domain domaindroptest is 'About to drop this..'; diff --git a/src/test/regress/sql/enum.sql b/src/test/regress/sql/enum.sql index 88a835e..4f9ebb7 100644 --- a/src/test/regress/sql/enum.sql +++ b/src/test/regress/sql/enum.sql @@ -4,6 +4,10 @@ CREATE TYPE rainbow AS ENUM ('red', 'orange', 'yellow', 'green', 'blue', 'purple'); +CREATE TYPE rainbow AS ENUM ('red', 'orange', 'yellow', 'green', 'blue', 'purple'); + +CREATE TYPE IF NOT EXISTS rainbow AS ENUM ('red', 'orange', 'yellow', 'green', 'blue', 'purple'); + -- -- Did it create the right number of rows? -- diff --git a/src/test/regress/sql/event_trigger.sql b/src/test/regress/sql/event_trigger.sql index 11d2ce5..190e1b3 100644 --- a/src/test/regress/sql/event_trigger.sql +++ b/src/test/regress/sql/event_trigger.sql @@ -18,6 +18,14 @@ create event trigger regress_event_trigger on elephant_bootstrap create event trigger regress_event_trigger on ddl_command_start execute procedure test_event_trigger(); +-- FAIL +create event trigger regress_event_trigger on ddl_command_start + execute procedure test_event_trigger(); + +-- FAIL, but skipp +create event trigger if not exists regress_event_trigger on ddl_command_start + execute procedure test_event_trigger(); + -- OK create event trigger regress_event_trigger_end on ddl_command_end execute procedure test_event_trigger(); diff --git a/src/test/regress/sql/rangetypes.sql b/src/test/regress/sql/rangetypes.sql index fad843a..32d5b95 100644 --- a/src/test/regress/sql/rangetypes.sql +++ b/src/test/regress/sql/rangetypes.sql @@ -1,6 +1,8 @@ -- Tests for range data types. create type textrange as range (subtype=text, collation="C"); +create type textrange as range (subtype=text, collation="C"); +create type if not exists textrange as range (subtype=text, collation="C"); -- -- test input parser diff --git a/src/test/regress/sql/roles.sql b/src/test/regress/sql/roles.sql new file mode 100644 index 0000000..e3a91fc --- /dev/null +++ b/src/test/regress/sql/roles.sql @@ -0,0 +1,8 @@ +CREATE ROLE test_nonsuperuser; +CREATE ROLE test_superuser SUPERUSER; +SELECT * FROM pg_authid WHERE rolname ~ '^test_'; +CREATE ROLE test_nonsuperuser; +CREATE ROLE IF NOT EXISTS test_nonsuperuser; +ALTER ROLE test_nonsuperuser LOGIN PASSWORD 'pass'; +ALTER ROLE test_superuser LOGIN PASSWORD 'pass'; +SELECT * FROM pg_authid WHERE rolname ~ '^test_'; diff --git a/src/test/regress/sql/sequence.sql b/src/test/regress/sql/sequence.sql index be5e9a9..8d3b700 100644 --- a/src/test/regress/sql/sequence.sql +++ b/src/test/regress/sql/sequence.sql @@ -59,6 +59,7 @@ SELECT nextval('serialTest2_f6_seq'); -- basic sequence operations using both text and oid references CREATE SEQUENCE sequence_test; +CREATE SEQUENCE IF NOT EXISTS sequence_test; SELECT nextval('sequence_test'::text); SELECT nextval('sequence_test'::regclass); diff --git a/src/test/regress/sql/tsdicts.sql b/src/test/regress/sql/tsdicts.sql index 55afcec..2f66006 100644 --- a/src/test/regress/sql/tsdicts.sql +++ b/src/test/regress/sql/tsdicts.sql @@ -6,6 +6,16 @@ CREATE TEXT SEARCH DICTIONARY ispell ( DictFile=ispell_sample, AffFile=ispell_sample ); +CREATE TEXT SEARCH DICTIONARY ispell ( + Template=ispell, + DictFile=ispell_sample, + AffFile=ispell_sample +); +CREATE TEXT SEARCH DICTIONARY IF NOT EXISTS ispell ( + Template=ispell, + DictFile=ispell_sample, + AffFile=ispell_sample +); SELECT ts_lexize('ispell', 'skies'); SELECT ts_lexize('ispell', 'bookings'); @@ -73,6 +83,12 @@ SELECT ts_lexize('thesaurus', 'one'); CREATE TEXT SEARCH CONFIGURATION ispell_tst ( COPY=english ); +CREATE TEXT SEARCH CONFIGURATION ispell_tst ( + COPY=english +); +CREATE TEXT SEARCH CONFIGURATION IF NOT EXISTS ispell_tst ( + COPY=english +); ALTER TEXT SEARCH CONFIGURATION ispell_tst ALTER MAPPING FOR word, numword, asciiword, hword, numhword, asciihword, hword_part, hword_numpart, hword_asciipart