PostgreSQL reusing value from long calculation in CASE statement

Question

I have the following:

SELECT
 CASE column1
  WHEN some_long_calc THEN 10
  ELSE some_long_calc + 2*PI
  END AS mycalc,

How can I "save" some_long_calc as a local variable so I don't need to run it twice?

I am using atan2 and I want to keep my angle range between 0 and 2pi

http://stackoverflow.com/questions/3157753/how-to-declare-local-variables-in-postgresql — , Apr 16 '16 at 04:58

Lukasz Szozda · Accepted Answer · 2016-04-16T07:19:52.110

3

First of all I guess that query optimizer is smart enough to spot the same deterministic expressions and do not calculate it twice.

If this is not applicable you could use LATERAL:

SELECT *,
  CASE column1
     WHEN sub.long_calc THEN 10
     ELSE sub.long_calc + 2 * 3.14
  END AS mycalc
FROM tab t
,LATERAL (VALUES(t.a+t.b+t.c)) AS sub(long_calc);

SqlFiddleDemo

Output:

╔═════╦══════════╦════╦════╦════╦════════════╦════════╗
║ id  ║ column1  ║ a  ║ b  ║ c  ║ long_calc  ║ mycalc ║
╠═════╬══════════╬════╬════╬════╬════════════╬════════╣
║  1  ║       6  ║ 1  ║ 2  ║ 3  ║         6  ║ 10     ║
║  2  ║      20  ║ 2  ║ 3  ║ 4  ║         9  ║ 15.28  ║
╚═════╩══════════╩════╩════╩════╩════════════╩════════╝

You could replace VALUES with simple SELECT or function call:

-- any query
,LATERAL (SELECT t.a+t.b+t.c) AS sub(long_calc)
-- function
,LATERAL random() AS sub(long_calc)
-- function with parameter passing
,LATERAL sin(t.a) AS sub(long_calc)

SqlFiddleDemo2

EDIT:

SELECT id
      ,sub2.long_calc_rand     -- calculated once
      ,random() AS rand        -- calculated every time
FROM tab t
,LATERAL random() AS sub2(long_calc_rand);

SqlFiddleDemo3

Output:

╔═════╦═════════════════════╦════════════════════╗
║ id  ║   long_calc_rand    ║        rand        ║
╠═════╬═════════════════════╬════════════════════╣
║  1  ║ 0.3426254219375551  ║ 0.8861959744244814 ║
║  2  ║ 0.3426254219375551  ║ 0.8792812027968466 ║
║  3  ║ 0.3426254219375551  ║ 0.8123061805963516 ║
╚═════╩═════════════════════╩════════════════════╝

edited Apr 16 '16 at 07:19

answered Apr 16 '16 at 06:57

Lukasz Szozda

162,964
23
234
275

The calc itself is like 15 lines so it looks ugly :D – DaynaJuliana Apr 16 '16 at 06:59
1

@DaynaJuliana That is why `LATERAL` is nice. You have to place ugly code once :) – Lukasz Szozda Apr 16 '16 at 07:03
1

this is great ! is the `VALUES` necessary? – DaynaJuliana Apr 16 '16 at 07:07
what is a function call? can i just use a bunch of postgresql functions? right now thats inside a select clause – DaynaJuliana Apr 16 '16 at 07:09
1

@DaynaJuliana Yes, you could use function and pass arguments from main query too :) – Lukasz Szozda Apr 16 '16 at 07:10
would this go before or after my joins/ordering? im assuming its after the FROM – DaynaJuliana Apr 16 '16 at 07:19
1

You could place it after last `JOIN`. If you have multiple complex joins "combine" them in one using subquery/CTE – Lukasz Szozda Apr 16 '16 at 07:21
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/109302/discussion-between-daynajuliana-and-lad2025). – DaynaJuliana Apr 16 '16 at 07:22
@DaynaJuliana Sorry I have to go now. If you need further assistance please consider asking new question. [I believe your original one is solved](http://meta.stackexchange.com/questions/43478/exit-strategies-for-chameleon-questions) :) – Lukasz Szozda Apr 16 '16 at 07:24
Hahaha all good ! I figured it out! Will edit my question to help others – DaynaJuliana Apr 16 '16 at 07:31

PostgreSQL reusing value from long calculation in CASE statement

1 Answers1