Re: Can I trigger an action from a coalesce ?

On 2020-02-22 16:02:06 -0500, stan wrote:
> I have a case where if a value does not exist, I am going to use a default,
> which is easy with coalesce. But I would like to warn the user that a
> default has been supplied. The default value is reasonable, and could
> actually come from the source table, so I can't just check the value.
> I'd like to do a raise NOTICE, if the default portion of the coalesce fires.

Might I suggest a different approach?

Instead of raising a notice, add an additional column. Something like
this:

wds=> create table mytable (id serial, value int);
CREATE TABLE
Time: 127.124 ms
wds=> insert into mytable (value) values (2), (23), (null), (42), (78);
INSERT 0 5
Time: 48.223 ms
wds=> select * from mytable;
╔════╤═══════╗
║ id │ value ║
╟────┼───────╢
║  1 │     2 ║
║  2 │    23 ║
║  3 │   (∅) ║
║  4 │    42 ║
║  5 │    78 ║
╚════╧═══════╝
(5 rows)

Time: 0.657 ms
wds=> select id, coalesce(value, 42) as value, value is null as value_was_null
wds-> from mytable;
╔════╤═══════╤════════════════╗
║ id │ value │ value_was_null ║
╟────┼───────┼────────────────╢
║  1 │     2 │ f              ║
║  2 │    23 │ f              ║
║  3 │    42 │ t              ║
║  4 │    42 │ f              ║
║  5 │    78 │ f              ║
╚════╧═══════╧════════════════╝
(5 rows)

Time: 0.247 ms

This lets the user distinguish the real 42 with id 4 from the
substituted 42 with id 3. I think this would be awkward with a notice.
At most you could tell the user "some values were substituted", but not
which ones (at least not if the query can return a large number of
rows).

        hp


--
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | hjp@hjp.at         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"