Re: Can I trigger an action from a coalesce ?
On 2020-02-24 05:20:49 +, sivapostg...@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. |_|_) || | | | h...@hjp.at |-- Charles Stross, "Creative writing __/ | http://www.hjp.at/ | challenge!" signature.asc Description: PGP signature
Re: Can I trigger an action from a coalesce ?
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. On Sunday, 23 February, 2020, 04:52:11 pm IST, Peter J. Holzer wrote: 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. |_|_) | | | | | h...@hjp.at | -- Charles Stross, "Creative writing __/ | http://www.hjp.at/ | challenge!"
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. |_|_) || | | | h...@hjp.at |-- Charles Stross, "Creative writing __/ | http://www.hjp.at/ | challenge!" signature.asc Description: PGP signature
Re: Can I trigger an action from a coalesce ?
> On Feb 22, 2020, at 14:36, Tom Lane 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, and that behavior isn't (afaik) a promise. -- -- Christophe Pettus x...@thebuild.com
Re: Can I trigger an action from a coalesce ?
Christophe Pettus writes: >> On Feb 22, 2020, at 14:02, Tom Lane 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
Re: Can I trigger an action from a coalesce ?
> On Feb 22, 2020, at 14:02, Tom Lane 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?) -- -- Christophe Pettus x...@thebuild.com
Re: Can I trigger an action from a coalesce ?
Christophe Pettus 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
Re: Can I trigger an action from a coalesce ?
> On Feb 22, 2020, at 13:33, stan 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
x...@thebuild.com
Re: Can I trigger an action from a coalesce ?
On Sat, Feb 22, 2020 at 01:06:57PM -0800, Christophe Pettus wrote: > > > > On Feb 22, 2020, at 13:05, Adrian Klaver 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
Re: Can I trigger an action from a coalesce ?
> On Feb 22, 2020, at 13:05, Adrian Klaver 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 x...@thebuild.com
Re: Can I trigger an action from a coalesce ?
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.kla...@aklaver.com
