Re: Can I trigger an action from a coalesce ?

Christophe Pettus <xof@thebuild.com> 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