Christophe Pettus <xof@xxxxxxxxxxxx> writes:
> Something like this:
> create function supply_default() returns int as $$
> begin
> raise notice 'Supplied default';
> return 1;
> end;
> $$ immutable language plpgsql;
It's a really bad idea to mark a function that has side-effects
(i.e., emitting a NOTICE) as immutable, especially if the occurrence
of the side-effect at well-defined times is exactly what you're
desirous of.
> xof=# create table t ( i integer default supply_default(), t text );
> CREATE TABLE
> xof=# insert into t(i, t) values (2, 'text');
> INSERT 0 1
> xof=# insert into t(t) values ('text');
> NOTICE: Supplied default
> INSERT 0 1
Other than the mislabeled volatility, I think this will mostly work.
Another possibility is to use a before-row-insert trigger
that does something like
if new.i is null then
begin
new.i := whatever;
raise notice 'Supplied default';
end if;
This seems cleaner in principle, but a problem is that it can't tell
an inserted-by-default NULL from one that was intentionally supplied.
That might be OK if you never want the field to be null anyway.
regards, tom lane
