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(at)hjp(dot)at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"