Column Default Clause and User Defined Functions

On Jun 27, 2007, at 18:18 , Keary Suska wrote:

Is it possible to have a user-defined function (a plpqsql function)
as the
argument to a default clause that issues SELECTs on other tables?

Not according to the documentation:

http://www.postgresql.org/docs/8.2/interactive/sql-createtable.html

DEFAULT default_expr

The DEFAULT clause assigns a default data value for the column
whose column definition it appears within. The value is any
variable-free expression (subqueries and cross-references to other
columns in the current table are not allowed). The data type of the
default expression must match the data type of the column.

The default expression will be used in any insert operation
that does not specify a value for the column. If there is no
default for a column, then the default is null.

Michael Glaesemann
grzm seespotcode net

Michael Glaesemann <grzm@seespotcode.net> writes:

On Jun 27, 2007, at 18:18 , Keary Suska wrote:

Is it possible to have a user-defined function (a plpqsql function)
as the
argument to a default clause that issues SELECTs on other tables?

Not according to the documentation:
http://www.postgresql.org/docs/8.2/interactive/sql-createtable.html

What those docs say is that you can't have a naked sub-SELECT in the
DEFAULT expression.  This is to some extent an implementation limitation
--- we don't do planning on DEFAULT expressions.  You can definitely get
around it by hiding the sub-SELECT in a function.

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.

regards, tom lane

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