Hi,
here's a fix for a _very_ longstanding bug in PostgreSQL.
According to SQL:2003 DEFAULT may only contain
certain functional expressions and constant literals.
Please, note the year of the standard. Or I know a better one,
PostgreSQL is not even SQL92 compliant in this regard, after 14 years!
http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt
Please review and apply immediately.
Or not, it's just a bitter and late (because of my bitterness) response
to the rejection of my IDENTITY/GENERATED patches.
Where's the much praised standard behaviour on standard syntax?
So much for hypocrisy.
--
----------------------------------
Zoltán Böszörményi
Cybertec Geschwinde & Schönig GmbH
http://www.postgresql.at/
--- pgsql.orig/src/backend/catalog/heap.c 2007-05-15 09:34:25.000000000 +0200
+++ pgsql/src/backend/catalog/heap.c 2007-05-18 21:33:04.000000000 +0200
@@ -1935,6 +1935,43 @@
errmsg("cannot use column references in default expression")));
/*
+ * Make sure default expr may contain only
+ * standard compliant functions as of SQL:2003:
+ * - CURRENT_DATE
+ * - CURRENT_TIME[ ( precision ) ]
+ * - CURRENT_TIMESTAMP[ ( precision ) ]
+ * - LOCALTIME[ ( precision ) ]
+ * - LOCALTIMESTAMP[ ( precision ) ]
+ * - as a PostgreSQL extension,
+ * all others that call now() implicitely or explicitely
+ * - USER
+ * - CURRENT_USER
+ * - CURRENT_ROLE
+ * - SESSION_USER
+ * with two other PostgreSQL extensions:
+ * - nextval() so SERIALs work
+ * - any immutable functions to pave the way for GENERATED columns
+ * Please note that PostgreSQL lacks SYSTEM_USER and CURRENT_PATH.
+ */
+ if (is_opclause(expr)) {
+ OpExpr *clause = (OpExpr *)expr;
+
+ switch (clause->opfuncid)
+ {
+ case 745: /* current_user */
+ case 746: /* session_user */
+ case 1299: /* now() */
+ case 1574: /* nextval() */
+ break;
+ default:
+ if (contain_mutable_functions(expr))
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
+ errmsg("cannot use non-IMMUTABLE functions in default expression")));
+ }
+ }
+
+ /*
* It can't return a set either.
*/
if (expression_returns_set(expr))
