Patch to add support of "IF NOT EXISTS" to others "CREATE" statements
Started by Pavel Stehuleover 12 years ago2 messages
Re: Patch to add support of "IF NOT EXISTS" to others "CREATE" statements
On Tue, Sep 10, 2013 at 11:25 AM, Pavel Stehule <pavel.stehule@gmail.com>wrote:
I tested this patch and it is not patchable now. Please, can you fix patch?
Rebased.
Regards,
--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL
Show quoted text
Timbira: http://www.timbira.com.br
Blog sobre TI: http://fabriziomello.blogspot.com
Perfil Linkedin: http://br.linkedin.com/in/fabriziomello
Twitter: http://twitter.com/fabriziomello
Attachments:
create_if_not_exists_v8.patchtext/x-diff; charset=US-ASCII; name=create_if_not_exists_v8.patchDownload
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
<refsynopsisdiv>
<synopsis>
-CREATE AGGREGATE <replaceable class="parameter">name</replaceable> ( [ <replaceable class="parameter">argmode</replaceable> ] [ <replaceable class="parameter">arg_name</replaceable> ] <replaceable class="parameter">arg_data_type</replaceable> [ , ... ] ) (
+CREATE AGGREGATE [ IF NOT EXISTS ] <replaceable class="parameter">name</replaceable> ( [ <replaceable class="parameter">argmode</replaceable> ] [ <replaceable class="parameter">arg_name</replaceable> ] <replaceable class="parameter">arg_data_type</replaceable> [ , ... ] ) (
SFUNC = <replaceable class="PARAMETER">sfunc</replaceable>,
STYPE = <replaceable class="PARAMETER">state_data_type</replaceable>
[ , SSPACE = <replaceable class="PARAMETER">state_data_size</replaceable> ]
@@ -32,7 +32,7 @@ CREATE AGGREGATE <replaceable class="parameter">name</replaceable> ( [ <replacea
<phrase>or the old syntax</phrase>
-CREATE AGGREGATE <replaceable class="PARAMETER">name</replaceable> (
+CREATE AGGREGATE [ IF NOT EXISTS ] <replaceable class="PARAMETER">name</replaceable> (
BASETYPE = <replaceable class="PARAMETER">base_type</replaceable>,
SFUNC = <replaceable class="PARAMETER">sfunc</replaceable>,
STYPE = <replaceable class="PARAMETER">state_data_type</replaceable>
@@ -179,6 +179,16 @@ SELECT col FROM tab ORDER BY col USING sortop LIMIT 1;
<variablelist>
<varlistentry>
+ <term><literal>IF NOT EXISTS</literal></term>
+ <listitem>
+ <para>
+ Do nothing (except issuing a notice) if an aggregate function with
+ the same argument types already exists.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
<term><replaceable class="PARAMETER">name</replaceable></term>
<listitem>
<para>
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 @@
<refsynopsisdiv>
<synopsis>
-CREATE CAST (<replaceable>source_type</replaceable> AS <replaceable>target_type</replaceable>)
+CREATE CAST [ IF NOT EXISTS ] (<replaceable>source_type</replaceable> AS <replaceable>target_type</replaceable>)
WITH FUNCTION <replaceable>function_name</replaceable> (<replaceable>argument_type</replaceable> [, ...])
[ AS ASSIGNMENT | AS IMPLICIT ]
-CREATE CAST (<replaceable>source_type</replaceable> AS <replaceable>target_type</replaceable>)
+CREATE CAST [ IF NOT EXISTS ] (<replaceable>source_type</replaceable> AS <replaceable>target_type</replaceable>)
WITHOUT FUNCTION
[ AS ASSIGNMENT | AS IMPLICIT ]
-CREATE CAST (<replaceable>source_type</replaceable> AS <replaceable>target_type</replaceable>)
+CREATE CAST [ IF NOT EXISTS ] (<replaceable>source_type</replaceable> AS <replaceable>target_type</replaceable>)
WITH INOUT
[ AS ASSIGNMENT | AS IMPLICIT ]
</synopsis>
@@ -171,6 +171,16 @@ SELECT CAST ( 2 AS numeric ) + 4.0;
<title>Parameters</title>
<variablelist>
+ <varlistentry>
+ <term><literal>IF NOT EXISTS</literal></term>
+ <listitem>
+ <para>
+ Do nothing (except issuing a notice) if a cast with the same
+ from and to type already exists.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><replaceable>source_type</replaceable></term>
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 @@
<refsynopsisdiv>
<synopsis>
-CREATE COLLATION <replaceable>name</replaceable> (
+CREATE COLLATION [ IF NOT EXISTS ] <replaceable>name</replaceable> (
[ LOCALE = <replaceable>locale</replaceable>, ]
[ LC_COLLATE = <replaceable>lc_collate</replaceable>, ]
[ LC_CTYPE = <replaceable>lc_ctype</replaceable> ]
)
-CREATE COLLATION <replaceable>name</replaceable> FROM <replaceable>existing_collation</replaceable>
+CREATE COLLATION [ IF NOT EXISTS ] <replaceable>name</replaceable> FROM <replaceable>existing_collation</replaceable>
</synopsis>
</refsynopsisdiv>
@@ -47,6 +47,16 @@ CREATE COLLATION <replaceable>name</replaceable> FROM <replaceable>existing_coll
<title>Parameters</title>
<variablelist>
+ <varlistentry>
+ <term><literal>IF NOT EXISTS</literal></term>
+ <listitem>
+ <para>
+ Do nothing (except issuing a notice) if an collation with the same
+ name already exists.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><replaceable>name</replaceable></term>
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
<refsynopsisdiv>
<synopsis>
-CREATE DOMAIN <replaceable class="parameter">name</replaceable> [ AS ] <replaceable class="parameter">data_type</replaceable>
+CREATE DOMAIN [ IF NOT EXISTS ] <replaceable class="parameter">name</replaceable> [ AS ] <replaceable class="parameter">data_type</replaceable>
[ COLLATE <replaceable>collation</replaceable> ]
[ DEFAULT <replaceable>expression</replaceable> ]
[ <replaceable class="PARAMETER">constraint</replaceable> [ ... ] ]
@@ -71,6 +71,16 @@ CREATE DOMAIN <replaceable class="parameter">name</replaceable> [ AS ] <replacea
<variablelist>
<varlistentry>
+ <term><literal>IF NOT EXISTS</literal></term>
+ <listitem>
+ <para>
+ Do nothing (except issuing a notice) if a domain with the same name
+ already exists.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
<term><replaceable class="parameter">name</replaceable></term>
<listitem>
<para>
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
<refsynopsisdiv>
<synopsis>
-CREATE EVENT TRIGGER <replaceable class="PARAMETER">name</replaceable>
+CREATE EVENT TRIGGER [ IF NOT EXISTS ] <replaceable class="PARAMETER">name</replaceable>
ON <replaceable class="PARAMETER">event</replaceable>
[ WHEN <replaceable class="PARAMETER">filter_variable</replaceable> IN (filter_value [, ... ]) [ AND ... ] ]
EXECUTE PROCEDURE <replaceable class="PARAMETER">function_name</replaceable>()
@@ -46,6 +46,16 @@ CREATE EVENT TRIGGER <replaceable class="PARAMETER">name</replaceable>
<variablelist>
<varlistentry>
+ <term><literal>IF NOT EXISTS</literal></term>
+ <listitem>
+ <para>
+ Do nothing (except issuing a notice) if a event trigger the
+ same name already exists.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
<term><replaceable class="parameter">name</replaceable></term>
<listitem>
<para>
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
<refsynopsisdiv>
<synopsis>
-CREATE OPERATOR <replaceable>name</replaceable> (
+CREATE OPERATOR [ IF NOT EXISTS ] <replaceable>name</replaceable> (
PROCEDURE = <replaceable class="parameter">function_name</replaceable>
[, LEFTARG = <replaceable class="parameter">left_type</replaceable> ] [, RIGHTARG = <replaceable class="parameter">right_type</replaceable> ]
[, COMMUTATOR = <replaceable class="parameter">com_op</replaceable> ] [, NEGATOR = <replaceable class="parameter">neg_op</replaceable> ]
@@ -117,6 +117,16 @@ CREATE OPERATOR <replaceable>name</replaceable> (
<variablelist>
<varlistentry>
+ <term><literal>IF NOT EXISTS</literal></term>
+ <listitem>
+ <para>
+ Do nothing (except issuing a notice) if an operator with the same
+ name already exists.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
<term><replaceable class="parameter">name</replaceable></term>
<listitem>
<para>
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
<refsynopsisdiv>
<synopsis>
-CREATE ROLE <replaceable class="PARAMETER">name</replaceable> [ [ WITH ] <replaceable class="PARAMETER">option</replaceable> [ ... ] ]
+CREATE ROLE [ IF NOT EXISTS ] <replaceable class="PARAMETER">name</replaceable> [ [ WITH ] <replaceable class="PARAMETER">option</replaceable> [ ... ] ]
<phrase>where <replaceable class="PARAMETER">option</replaceable> can be:</phrase>
@@ -69,6 +69,17 @@ CREATE ROLE <replaceable class="PARAMETER">name</replaceable> [ [ WITH ] <replac
<title>Parameters</title>
<variablelist>
+
+ <varlistentry>
+ <term><literal>IF NOT EXISTS</literal></term>
+ <listitem>
+ <para>
+ Do nothing (except issuing a notice) if a role with the same name
+ already exists.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><replaceable class="parameter">name</replaceable></term>
<listitem>
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
<refsynopsisdiv>
<synopsis>
-CREATE [ TEMPORARY | TEMP ] SEQUENCE <replaceable class="parameter">name</replaceable> [ INCREMENT [ BY ] <replaceable class="parameter">increment</replaceable> ]
+CREATE [ TEMPORARY | TEMP ] [ IF NOT EXISTS ] SEQUENCE <replaceable class="parameter">name</replaceable> [ INCREMENT [ BY ] <replaceable class="parameter">increment</replaceable> ]
[ MINVALUE <replaceable class="parameter">minvalue</replaceable> | NO MINVALUE ] [ MAXVALUE <replaceable class="parameter">maxvalue</replaceable> | NO MAXVALUE ]
[ START [ WITH ] <replaceable class="parameter">start</replaceable> ] [ CACHE <replaceable class="parameter">cache</replaceable> ] [ [ NO ] CYCLE ]
[ OWNED BY { <replaceable class="parameter">table_name</replaceable>.<replaceable class="parameter">column_name</replaceable> | NONE } ]
@@ -90,6 +90,16 @@ SELECT * FROM <replaceable>name</replaceable>;
</varlistentry>
<varlistentry>
+ <term><literal>IF NOT EXISTS</literal></term>
+ <listitem>
+ <para>
+ Do nothing (except issuing a notice) if a sequence with the same name
+ already exists.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
<term><replaceable class="parameter">name</replaceable></term>
<listitem>
<para>
@@ -343,8 +353,8 @@ END;
</listitem>
<listitem>
<para>
- The <literal>OWNED BY</> clause is a <productname>PostgreSQL</>
- extension.
+ The <literal>OWNED BY</> and <literal>IF NOT EXISTS</> clause
+ is a <productname>PostgreSQL</> extension.
</para>
</listitem>
</itemizedlist></para>
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
<refsynopsisdiv>
<synopsis>
-CREATE TEXT SEARCH CONFIGURATION <replaceable class="parameter">name</replaceable> (
+CREATE TEXT SEARCH CONFIGURATION [ IF NOT EXISTS ] <replaceable class="parameter">name</replaceable> (
PARSER = <replaceable class="parameter">parser_name</replaceable> |
COPY = <replaceable class="parameter">source_config</replaceable>
)
@@ -66,6 +66,16 @@ CREATE TEXT SEARCH CONFIGURATION <replaceable class="parameter">name</replaceabl
<variablelist>
<varlistentry>
+ <term><literal>IF NOT EXISTS</literal></term>
+ <listitem>
+ <para>
+ Do nothing (except issuing a notice) if a text search configuration
+ with the same name already exists.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
<term><replaceable class="parameter">name</replaceable></term>
<listitem>
<para>
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
<refsynopsisdiv>
<synopsis>
-CREATE TEXT SEARCH DICTIONARY <replaceable class="parameter">name</replaceable> (
+CREATE TEXT SEARCH DICTIONARY [ IF NOT EXISTS ] <replaceable class="parameter">name</replaceable> (
TEMPLATE = <replaceable class="parameter">template</replaceable>
[, <replaceable class="parameter">option</replaceable> = <replaceable class="parameter">value</replaceable> [, ... ]]
)
@@ -59,6 +59,16 @@ CREATE TEXT SEARCH DICTIONARY <replaceable class="parameter">name</replaceable>
<variablelist>
<varlistentry>
+ <term><literal>IF NOT EXISTS</literal></term>
+ <listitem>
+ <para>
+ Do nothing (except issuing a notice) if a text search dictionary
+ with the same name already exists.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
<term><replaceable class="parameter">name</replaceable></term>
<listitem>
<para>
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
<refsynopsisdiv>
<synopsis>
-CREATE TEXT SEARCH PARSER <replaceable class="parameter">name</replaceable> (
+CREATE TEXT SEARCH PARSER [ IF NOT EXISTS ] <replaceable class="parameter">name</replaceable> (
START = <replaceable class="parameter">start_function</replaceable> ,
GETTOKEN = <replaceable class="parameter">gettoken_function</replaceable> ,
END = <replaceable class="parameter">end_function</replaceable> ,
@@ -64,6 +64,16 @@ CREATE TEXT SEARCH PARSER <replaceable class="parameter">name</replaceable> (
<variablelist>
<varlistentry>
+ <term><literal>IF NOT EXISTS</literal></term>
+ <listitem>
+ <para>
+ Do nothing (except issuing a notice) if a text search parser
+ with the same name already exists.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
<term><replaceable class="parameter">name</replaceable></term>
<listitem>
<para>
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
<refsynopsisdiv>
<synopsis>
-CREATE TEXT SEARCH TEMPLATE <replaceable class="parameter">name</replaceable> (
+CREATE TEXT SEARCH TEMPLATE [ IF NOT EXISTS ] <replaceable class="parameter">name</replaceable> (
[ INIT = <replaceable class="parameter">init_function</replaceable> , ]
LEXIZE = <replaceable class="parameter">lexize_function</replaceable>
)
@@ -65,6 +65,16 @@ CREATE TEXT SEARCH TEMPLATE <replaceable class="parameter">name</replaceable> (
<variablelist>
<varlistentry>
+ <term><literal>IF NOT EXISTS</literal></term>
+ <listitem>
+ <para>
+ Do nothing (except issuing a notice) if a text search template with
+ the same name already exists.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
<term><replaceable class="parameter">name</replaceable></term>
<listitem>
<para>
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
<refsynopsisdiv>
<synopsis>
-CREATE TYPE <replaceable class="parameter">name</replaceable> AS
+CREATE TYPE [ IF NOT EXISTS ] <replaceable class="parameter">name</replaceable> AS
( [ <replaceable class="PARAMETER">attribute_name</replaceable> <replaceable class="PARAMETER">data_type</replaceable> [ COLLATE <replaceable>collation</replaceable> ] [, ... ] ] )
-CREATE TYPE <replaceable class="parameter">name</replaceable> AS ENUM
+CREATE TYPE [ IF NOT EXISTS ] <replaceable class="parameter">name</replaceable> AS ENUM
( [ '<replaceable class="parameter">label</replaceable>' [, ... ] ] )
-CREATE TYPE <replaceable class="parameter">name</replaceable> AS RANGE (
+CREATE TYPE [ IF NOT EXISTS ] <replaceable class="parameter">name</replaceable> AS RANGE (
SUBTYPE = <replaceable class="parameter">subtype</replaceable>
[ , SUBTYPE_OPCLASS = <replaceable class="parameter">subtype_operator_class</replaceable> ]
[ , COLLATION = <replaceable class="parameter">collation</replaceable> ]
@@ -35,7 +35,7 @@ CREATE TYPE <replaceable class="parameter">name</replaceable> AS RANGE (
[ , SUBTYPE_DIFF = <replaceable class="parameter">subtype_diff_function</replaceable> ]
)
-CREATE TYPE <replaceable class="parameter">name</replaceable> (
+CREATE TYPE [ IF NOT EXISTS ] <replaceable class="parameter">name</replaceable> (
INPUT = <replaceable class="parameter">input_function</replaceable>,
OUTPUT = <replaceable class="parameter">output_function</replaceable>
[ , RECEIVE = <replaceable class="parameter">receive_function</replaceable> ]
@@ -56,7 +56,7 @@ CREATE TYPE <replaceable class="parameter">name</replaceable> (
[ , COLLATABLE = <replaceable class="parameter">collatable</replaceable> ]
)
-CREATE TYPE <replaceable class="parameter">name</replaceable>
+CREATE TYPE [ IF NOT EXISTS ] <replaceable class="parameter">name</replaceable>
</synopsis>
</refsynopsisdiv>
@@ -484,6 +484,16 @@ CREATE TYPE <replaceable class="parameter">name</replaceable>
<variablelist>
<varlistentry>
+ <term><literal>IF NOT EXISTS</literal></term>
+ <listitem>
+ <para>
+ Do nothing (except issuing a notice) if a type with the same name
+ already exists.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
<term><replaceable class="parameter">name</replaceable></term>
<listitem>
<para>
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