![]() ![]() If the condition evaluates to true, the CASE expression will return the corresponding result set for that condition and stop evaluating the next expression. The CASE expression evaluates a list of conditions in sequence. Or why it's there there is plenty of dialogue on the hacker list and commit logs to keep you entertained.In the above syntax, every condition is a boolean expression that evaluates to be either true or false. One thing I love about PostgreSQL that you miss with closed-source and even with other open source projects, is that when you are curious about a new feature > Function Scan on pg_catalog.generate_series i (cost=0.00.10.00 rows=1000 width=0) Good old backwards compatible way: SELECT count(*)įROM (SELECT CASE i WHEN 1 THEN 'First' ELSE 'Not First' ENDįROM generate_series(1,10000000) AS i) AS f Aggregate (cost=12.50.12.51 rows=1 width=8) Output: CASE (generate_series(1, 10000000)) WHEN 1 THEN 'First'::text ELSE 'Not First'::text END Uses this new thing called a ProjectSet executor and takes ~1 second Aggregate (cost=32.52.32.53 rows=1 width=8) (actual time=1036.048.1036.048 rows=1 loops=1) Here is a quick test I did to compare the results and timing:įROM (SELECT CASE generate_series(1,10000000) WHEN 1 THEN 'First' ELSE 'Not First' END) AS f It should be noted though that, the new approach if you wanted to do something kinda crazy like that does seem to be faster than the standard approach. But that's still beingĭiscussed, and the code would reside in places not touched here, so that's Likely to instead prohibit SRFs in those places. That's because the SRFs areĮvaluated in a separate ProjectSet node. "arm" of the expression is not evaluated. Returning multiple rows from the expression, even when the SRF containing ![]() Not because it's particularlyĭesirable, but because it ends up working and there seems to be no argumentĬurrently the behavior for COALESCE and CASE containing SRFs has changed, That it's a side effect of other optimizations and introduction of new ProjectSet executor node.Īs a side effect, the previously prohibited case of multiple set returningĪrguments to a function, is now allowed. The answer seems reading from the commit notes, Why would I ever do that instead of something like this which will work in all versions? SELECT CASE i WHEN 1 THEN 'First' ELSE 'Not First' END Interesting, right, but how is this useful? In PostgreSQL 9.6 and below, you are slapped with an error message: ERROR: set-valued function called in context that cannot accept a set generate_series with CASE WHEN SELECT CASE generate_series(1,10) WHEN 1 THEN 'First' ELSE 'Not First' END Since this article was written, this feature has been removed, because it caused old logic that had sets from doing something different without warning. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |