I was not sure about the syntax, so this was a prototype. Now, like all
completed yet.
1) I think this syntax is wrong... Instead the common should be:
PREPARE [IF NOT EXISTS] ...
You right. Done.
2) All of CINE statements we emit a NOTICE skipping message, so you should
emit a message like it:
Done.
3) There are no regression tests
Done.
4) There are no docs
Done.
Regards.
--
Yury Zhuravlev
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company
diff --git a/doc/src/sgml/ref/prepare.sgml b/doc/src/sgml/ref/prepare.sgml
index dbce8f2..c52879f 100644
--- a/doc/src/sgml/ref/prepare.sgml
+++ b/doc/src/sgml/ref/prepare.sgml
@@ -26,7 +26,7 @@ PostgreSQL documentation
<refsynopsisdiv>
<synopsis>
-PREPARE <replaceable class="PARAMETER">name</replaceable> [ ( <replaceable class="PARAMETER">data_type</replaceable> [, ...] ) ] AS <replaceable class="PARAMETER">statement</replaceable>
+PREPARE [ IF NOT EXISTS ] <replaceable class="PARAMETER">name</replaceable> [ ( <replaceable class="PARAMETER">data_type</replaceable> [, ...] ) ] AS <replaceable class="PARAMETER">statement</replaceable>
</synopsis>
</refsynopsisdiv>
@@ -86,6 +86,15 @@ PREPARE <replaceable class="PARAMETER">name</replaceable> [ ( <replaceable class
<variablelist>
<varlistentry>
+ <term><literal>IF NOT EXISTS</></term>
+ <listitem>
+ <para>
+ Do not throw an error if a prepare statement with the same name already exists.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
<term><replaceable class="PARAMETER">name</replaceable></term>
<listitem>
<para>
diff --git a/src/backend/commands/prepare.c b/src/backend/commands/prepare.c
index cec37ce..019330f 100644
--- a/src/backend/commands/prepare.c
+++ b/src/backend/commands/prepare.c
@@ -59,6 +59,7 @@ PrepareQuery(PrepareStmt *stmt, const char *queryString)
int nargs;
Query *query;
List *query_list;
+ bool found;
int i;
/*
@@ -70,6 +71,30 @@ PrepareQuery(PrepareStmt *stmt, const char *queryString)
(errcode(ERRCODE_INVALID_PSTATEMENT_DEFINITION),
errmsg("invalid statement name: must not be empty")));
+ /* Find entry in hash table */
+ if(prepared_queries)
+ {
+ hash_search(prepared_queries,
+ stmt->name,
+ HASH_FIND,
+ &found);
+
+ /* Shouldn't get a duplicate entry */
+ if (found && stmt->if_not_exists)
+ {
+ ereport(NOTICE,
+ (errcode(ERRCODE_DUPLICATE_PSTATEMENT),
+ errmsg("prepared statement \"%s\" already exists, skipping",
+ stmt->name)));
+ return;
+ }
+ else if (found && !stmt->if_not_exists)
+ ereport(ERROR,
+ (errcode(ERRCODE_DUPLICATE_PSTATEMENT),
+ errmsg("prepared statement \"%s\" already exists",
+ stmt->name)));
+ }
+
/*
* Create the CachedPlanSource before we do parse analysis, since it needs
* to see the unmodified raw parse tree.
diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c
index df7c2fa..be8ac78 100644
--- a/src/backend/nodes/copyfuncs.c
+++ b/src/backend/nodes/copyfuncs.c
@@ -4021,6 +4021,7 @@ _copyPrepareStmt(const PrepareStmt *from)
COPY_STRING_FIELD(name);
COPY_NODE_FIELD(argtypes);
COPY_NODE_FIELD(query);
+ COPY_SCALAR_FIELD(if_not_exists);
return newnode;
}
diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c
index b9c3959..fbd248b 100644
--- a/src/backend/nodes/equalfuncs.c
+++ b/src/backend/nodes/equalfuncs.c
@@ -2017,6 +2017,7 @@ _equalPrepareStmt(const PrepareStmt *a, const PrepareStmt *b)
COMPARE_STRING_FIELD(name);
COMPARE_NODE_FIELD(argtypes);
COMPARE_NODE_FIELD(query);
+ COMPARE_SCALAR_FIELD(if_not_exists);
return true;
}
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index b9aeb31..e08d95f 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -9443,6 +9443,16 @@ PrepareStmt: PREPARE name prep_type_clause AS PreparableStmt
n->name = $2;
n->argtypes = $3;
n->query = $5;
+ n->if_not_exists = false;
+ $$ = (Node *) n;
+ }
+ | PREPARE IF_P NOT EXISTS name prep_type_clause AS PreparableStmt
+ {
+ PrepareStmt *n = makeNode(PrepareStmt);
+ n->name = $5;
+ n->argtypes = $6;
+ n->query = $8;
+ n->if_not_exists = true;
$$ = (Node *) n;
}
;
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 2fd0629..f08dee4 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -2986,6 +2986,7 @@ typedef struct PrepareStmt
char *name; /* Name of plan, arbitrary */
List *argtypes; /* Types of parameters (List of TypeName) */
Node *query; /* The query itself (as a raw parsetree) */
+ bool if_not_exists;
} PrepareStmt;
diff --git a/src/test/regress/expected/prepare.out b/src/test/regress/expected/prepare.out
index 7016e82..e870622 100644
--- a/src/test/regress/expected/prepare.out
+++ b/src/test/regress/expected/prepare.out
@@ -39,6 +39,23 @@ SELECT name, statement, parameter_types FROM pg_prepared_statements;
q2 | PREPARE q2 AS SELECT 2 AS b; | {}
(2 rows)
+-- if not exist
+PREPARE q1 AS SELECT 1 AS a;
+ERROR: prepared statement "q1" already exists
+PREPARE q1 AS SELECT 1 AS a;
+ERROR: prepared statement "q1" already exists
+PREPARE IF NOT EXISTS q1 AS SELECT 1 AS a;
+NOTICE: prepared statement "q1" already exists, skipping
+DEALLOCATE q1;
+PREPARE IF NOT EXISTS q1 AS SELECT 1 AS a;
+PREPARE IF NOT EXISTS q1 AS SELECT 1 AS a;
+NOTICE: prepared statement "q1" already exists, skipping
+EXECUTE q1;
+ a
+---
+ 1
+(1 row)
+
-- sql92 syntax
DEALLOCATE PREPARE q1;
SELECT name, statement, parameter_types FROM pg_prepared_statements;
diff --git a/src/test/regress/sql/prepare.sql b/src/test/regress/sql/prepare.sql
index 25f814b..b60ff77 100644
--- a/src/test/regress/sql/prepare.sql
+++ b/src/test/regress/sql/prepare.sql
@@ -20,6 +20,15 @@ EXECUTE q1;
PREPARE q2 AS SELECT 2 AS b;
SELECT name, statement, parameter_types FROM pg_prepared_statements;
+-- if not exist
+PREPARE q1 AS SELECT 1 AS a;
+PREPARE q1 AS SELECT 1 AS a;
+PREPARE IF NOT EXISTS q1 AS SELECT 1 AS a;
+DEALLOCATE q1;
+PREPARE IF NOT EXISTS q1 AS SELECT 1 AS a;
+PREPARE IF NOT EXISTS q1 AS SELECT 1 AS a;
+EXECUTE q1;
+
-- sql92 syntax
DEALLOCATE PREPARE q1;
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
