Re: Standard compliant DEFAULT clause - Mailing list pgsql-patches

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/