Re: Standard compliant DEFAULT clause

Zoltan Boszormenyi wrote:
> 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.

Antagonism will not help your cause.

Joshua D. Drake

>
>
> ------------------------------------------------------------------------
>
> --- 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))
>
>
> ------------------------------------------------------------------------
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster

--

=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/