Re: Column Default Clause and User Defined Functions

On Jun 28, 2007, at 0:01 , Tom Lane wrote:

> Whether that is a good idea is another question entirely ... it seems
> a bit questionable, but on the other hand time-varying defaults like
> "default now()" have time-honored usefulness, so I'm not quite sure
> why I feel uncomfortable with it.

I thought it was probably possible do so by wrapping it in a
function, but considered it in the same vein as wrapping queries in
functions to use them in CHECK constraints. It's a way to fake out
the checking and may lead to unexpected results if the data under the
subquery changes. It's not quite as serious as CHECK constraints are
used to ensure data integrity. And ISTM any subquery you'd put in a
DEFAULT could just as well go into your INSERT, where it's more
obvious what's going on. Though perhaps I'm being too conservative
here: it could be convenient to put a commonly used subquery into the
DEFAULT.

It looks like allowing functions other than those of the current date-
time variety (which Postgres does support) is an extension of the SQL
2003 spec (if I'm reading this correctly):

11.5 <default clause>
Function
Specify the default for a column, domain, or attribute.
Format
<default clause> ::= DEFAULT <default option>
<default option> ::=
<literal>
| <datetime value function>
| USER
| CURRENT_USER
| CURRENT_ROLE
| SESSION_USER
| SYSTEM_USER
| CURRENT_PATH
| <implicitly typed value specification>

Not that I would support limiting Postgres to a spec-strict
definition of this :)

Michael Glaesemann
grzm seespotcode net