Hello all,
yesterday i ran into a small problem:
http://andreas.scherbaum.la/blog/archives/346-create-language-if-not-exist.html
and was bugged to create a patch for PostgreSQL. So here is a first
version, still missing some things like documentation.
The attached patch for HEAD extends the CREATE LANGUAGE statement by an
IF NOT EXISTS option which in effect changes the raised error into a
notice.
Before i continue working on this patch i would like to know if this
extension has a chance to go into PG and what other changes i should
apply (beside the missing documentation).
Thank you
--
Andreas 'ads' Scherbaum
German PostgreSQL User Group
Index: src/backend/commands/proclang.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/commands/proclang.c,v
retrieving revision 1.77
diff -u -3 -p -r1.77 proclang.c
--- src/backend/commands/proclang.c 27 Mar 2008 03:57:33 -0000 1.77
+++ src/backend/commands/proclang.c 29 Mar 2008 01:18:59 -0000
@@ -75,9 +75,20 @@ CreateProceduralLanguage(CreatePLangStmt
if (SearchSysCacheExists(LANGNAME,
PointerGetDatum(languageName),
0, 0, 0))
- ereport(ERROR,
- (errcode(ERRCODE_DUPLICATE_OBJECT),
- errmsg("language \"%s\" already exists", languageName)));
+ {
+ if (!stmt->existing_ok)
+ {
+ ereport(ERROR,
+ (errcode(ERRCODE_DUPLICATE_OBJECT),
+ errmsg("language \"%s\" already exists", languageName)));
+ }
+ else
+ {
+ ereport(NOTICE,
+ (errmsg("language \"%s\" already exists, skipping", languageName)));
+ return;
+ }
+ }
/*
* If we have template information for the language, ignore the supplied
Index: src/backend/parser/gram.y
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/parser/gram.y,v
retrieving revision 2.611
diff -u -3 -p -r2.611 gram.y
--- src/backend/parser/gram.y 28 Mar 2008 00:21:55 -0000 2.611
+++ src/backend/parser/gram.y 29 Mar 2008 01:19:03 -0000
@@ -195,7 +195,7 @@ static Node *makeXmlExpr(XmlExprOp op, c
%type <ival> opt_lock lock_type cast_context
%type <boolean> opt_force opt_or_replace
opt_grant_grant_option opt_grant_admin_option
- opt_nowait opt_if_exists
+ opt_nowait opt_if_exists opt_if_not_exists
%type <list> OptRoleList
%type <defelt> OptRoleElem
@@ -2529,6 +2529,7 @@ CreatePLangStmt:
n->plhandler = NIL;
n->plvalidator = NIL;
n->pltrusted = false;
+ n->existing_ok = false;
$$ = (Node *)n;
}
| CREATE opt_trusted opt_procedural LANGUAGE ColId_or_Sconst
@@ -2540,6 +2541,30 @@ CreatePLangStmt:
n->plvalidator = $8;
n->pltrusted = $2;
/* LANCOMPILER is now ignored entirely */
+ n->existing_ok = false;
+ $$ = (Node *)n;
+ }
+ | CREATE opt_trusted opt_procedural LANGUAGE ColId_or_Sconst IF_P NOT EXISTS
+ {
+ CreatePLangStmt *n = makeNode(CreatePLangStmt);
+ n->plname = $5;
+ /* parameters are all to be supplied by system */
+ n->plhandler = NIL;
+ n->plvalidator = NIL;
+ n->pltrusted = false;
+ n->existing_ok = true;
+ $$ = (Node *)n;
+ }
+ | CREATE opt_trusted opt_procedural LANGUAGE ColId_or_Sconst
+ HANDLER handler_name opt_validator opt_lancompiler
+ {
+ CreatePLangStmt *n = makeNode(CreatePLangStmt);
+ n->plname = $5;
+ n->plhandler = $7;
+ n->plvalidator = $8;
+ n->pltrusted = $2;
+ /* LANCOMPILER is now ignored entirely */
+ n->existing_ok = true;
$$ = (Node *)n;
}
;
@@ -4493,6 +4518,10 @@ opt_if_exists: IF_P EXISTS { $$ = T
| /*EMPTY*/ { $$ = FALSE; }
;
+opt_if_not_exists: IF_P NOT EXISTS { $$ = TRUE; }
+ | /*EMPTY*/ { $$ = FALSE; }
+ ;
+
/*****************************************************************************
*
Index: src/include/nodes/parsenodes.h
===================================================================
RCS file: /projects/cvsroot/pgsql/src/include/nodes/parsenodes.h,v
retrieving revision 1.361
diff -u -3 -p -r1.361 parsenodes.h
--- src/include/nodes/parsenodes.h 21 Mar 2008 22:41:48 -0000 1.361
+++ src/include/nodes/parsenodes.h 29 Mar 2008 01:19:05 -0000
@@ -1262,6 +1262,7 @@ typedef struct CreatePLangStmt
List *plhandler; /* PL call handler function (qual. name) */
List *plvalidator; /* optional validator function (qual. name) */
bool pltrusted; /* PL is trusted */
+ bool existing_ok; /* skip error if already there? */
} CreatePLangStmt;
typedef struct DropPLangStmt
Index: src/interfaces/ecpg/preproc/preproc.y
===================================================================
RCS file: /projects/cvsroot/pgsql/src/interfaces/ecpg/preproc/preproc.y,v
retrieving revision 1.363
diff -u -3 -p -r1.363 preproc.y
--- src/interfaces/ecpg/preproc/preproc.y 27 Mar 2008 07:56:00 -0000 1.363
+++ src/interfaces/ecpg/preproc/preproc.y 29 Mar 2008 01:19:09 -0000
@@ -593,7 +593,7 @@ add_typedef(char *name, char * dimension
%type <str> CreatePLangStmt TriggerFuncArgs TriggerFuncArg simple_select
%type <str> ViewStmt LoadStmt CreatedbStmt createdb_opt_item ExplainableStmt
%type <str> createdb_opt_list opt_encoding OptInherit opt_equal
-%type <str> privilege_list privilege privilege_target opt_if_exists
+%type <str> privilege_list privilege privilege_target opt_if_exists opt_if_not_exists
%type <str> opt_grant_grant_option cursor_options DropOwnedStmt
%type <str> transaction_mode_list_or_empty transaction_mode_list
%type <str> function_with_argtypes_list function_with_argtypes IntConstVar
@@ -1878,6 +1878,11 @@ CreatePLangStmt: CREATE opt_trusted opt
| CREATE opt_trusted opt_procedural LANGUAGE ColId_or_Sconst
HANDLER handler_name opt_validator opt_lancompiler
{ $$ = cat_str(9, make_str("create"), $2, $3, make_str("language"), $5, make_str("handler"), $7, $8, $9); }
+ | CREATE opt_trusted opt_procedural LANGUAGE ColId_or_Sconst IF_P NOT EXISTS
+ { $$ = cat_str(6, make_str("create"), $2, $3, make_str("language"), $5, make_str("if not exists")); }
+ | CREATE opt_trusted opt_procedural LANGUAGE ColId_or_Sconst
+ HANDLER handler_name opt_validator opt_lancompiler IF_P NOT EXISTS
+ { $$ = cat_str(10, make_str("create"), $2, $3, make_str("language"), $5, make_str("handler"), $7, $8, $9, make_str("if not exists")); }
;
opt_trusted: TRUSTED { $$ = make_str("trusted"); }
@@ -2774,6 +2779,10 @@ opt_if_exists: IF_P EXISTS { $$ = make_s
| /* EMPTY */ { $$ = EMPTY; }
;
+opt_if_not_exists: IF_P NOT EXISTS { $$ = make_str("if not exists"); }
+ | /* EMPTY */ { $$ = EMPTY; }
+ ;
+
/*****************************************************************************
*
* QUERY:
--
Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-patches
