Thread: Can I trigger an action from a coalesce ?

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.

Anyone have a good way to accomplish this?


-- 
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
                        -- Benjamin Franklin

On 2/22/20 1:02 PM, 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.
> 
> Anyone have a good way to accomplish this?

No.


-- 
Adrian Klaver
adrian.klaver@aklaver.com

> On Feb 22, 2020, at 13:05, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
>
> On 2/22/20 1:02 PM, 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.
>> Anyone have a good way to accomplish this?
>
> No.

You can, of course, create a PL/pgSQL function and use that as the default.

--
-- Christophe Pettus
   xof@thebuild.com

On Sat, Feb 22, 2020 at 01:06:57PM -0800, Christophe Pettus wrote:
> 
> 
> > On Feb 22, 2020, at 13:05, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
> > 
> > On 2/22/20 1:02 PM, 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.
> >> Anyone have a good way to accomplish this?
> > 
> > No.
> 
> You can, of course, create a PL/pgSQL function and use that as the default.
I suppose you are suggesting that the function try the original SELECT, and
if it returns a NULL then retun the default AND do the raise NOTICE?

Or is there a simpler way?


-- 
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
                        -- Benjamin Franklin

> On Feb 22, 2020, at 13:33, stan <stanb@panix.com> wrote:
> I suppose you are suggesting that the function try the original SELECT, and
> if it returns a NULL then retun the default AND do the raise NOTICE?

Something like this:

create function supply_default() returns int as $$
begin
   raise notice 'Supplied default';
   return 1;
end;
$$ immutable language plpgsql;

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

--
-- Christophe Pettus
   xof@thebuild.com

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

> On Feb 22, 2020, at 14:02, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> 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.

True, and it doesn't actually need to be immutable here; just cut and pasted from the wrong example.

(That being said, I'm not coming up with a specific bad thing that a RAISE NOTICE in an immutable function will cause.
Isthere one?) 

--
-- Christophe Pettus
   xof@thebuild.com

Christophe Pettus <xof@thebuild.com> writes:
>> On Feb 22, 2020, at 14:02, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> 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.

> True, and it doesn't actually need to be immutable here; just cut and pasted from the wrong example.

> (That being said, I'm not coming up with a specific bad thing that a RAISE NOTICE in an immutable function will
cause. Is there one?) 

The problem that I'm worried about is premature evaluation of the
"immutable" function, causing the NOTICE to come out once during
query planning, independently of whether/how many times it should
come out during execution.

            regards, tom lane

> On Feb 22, 2020, at 14:36, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> The problem that I'm worried about is premature evaluation of the
> "immutable" function, causing the NOTICE to come out once during
> query planning, independently of whether/how many times it should
> come out during execution.

Ah, good point.  My solution also does assume that a DEFAULT expression is only evaluated if the default is required,
andthat behavior isn't (afaik) a promise. 

--
-- Christophe Pettus
   xof@thebuild.com

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!"

On 2020-02-24 05:20:49 +0000, sivapostgres@yahoo.com wrote:
> It could also be done by putting those values in square bracket, if substituted
> with default values.
> eg. [0]
>        4
>        45
>         [100]
> Values within square brackets are default values.

This would also work in many cases (especially if the values only have
to be displayed and not processed further).

In this case the OP wrote that "the default value is reasonable and
could actually come from the source table". I assumed that he had a
reason for this choice and wanted to preserve it.

        hp

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