Re: Making CASE error handling less surprising

pá 24. 7. 2020 v 19:13 odesílatel Andres Freund <andres(at)anarazel(dot)de> napsal:

> Hi,
>
> On 2020-07-24 19:03:30 +0200, Pavel Stehule wrote:
> > pá 24. 7. 2020 v 18:49 odesílatel Andres Freund <andres(at)anarazel(dot)de>
> napsal:
> > > Wouldn't the rule that I proposed earlier, namely that sub-expressions
> > > that involve only "proper" constants continue to get evaluated even
> > > within CASE, largely address that?
> > >
> >
> > It doesn't solve a possible performance problem with one shot (EXECUTE
> stmt
> > plpgsql) queries, or with parameterized queries
>
> What precisely are you thinking of here? Most expressions involving
> parameters would still get constant evaluated - it'd just be inside CASE
> etc that they wouldn't anymore? Do you think it's that common to have a
> parameter reference inside an expression inside a CASE where it's
> crucial that that parameter reference gets constant evaluated? I'd think
> that's a bit of a stretch.
>
> Your earlier example of a WHEN ... THEN upper('constant') ... would
> still have the upper('constant') be evaluated, because it doesn't
> involve a parameter. And e.g. THEN upper('constant') * $1 would also
> still have the upper('constant') be evaluated, just the multiplication
> with $1 wouldn't get evaluated.
>
>
> I'm not sure what you're concerned about with the one-shot bit?
>

Now query parameters are evaluated like constant.

I can imagine WHERE clause like WHERE col = CASE $1 WHEN true THEN
upper($2) ELSE $2 END

I remember applications that use these strange queries to support
parameterized behaviour - like case sensitive or case insensitive searching.

> Greetings,
>
> Andres Freund
>