pá 24. 7. 2020 v 19:13 odesílatel Andres Freund <andres@anarazel.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@anarazel.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.