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

waht does AS INT do ?

DaynaJuliana
– DaynaJuliana

2016-04-16 04:54:49 +00:00
Commented Apr 16, 2016 at 4:54 — DaynaJuliana
– DaynaJuliana, Commented Apr 16, 2016 at 4:54
@Eric - PostgreSQL does not have declared variables.

Phill
– Phill

2016-04-16 04:55:55 +00:00
Commented Apr 16, 2016 at 4:55 — Phill
– Phill, Commented Apr 16, 2016 at 4:55
stackoverflow.com/questions/3157753/…

user6178746
– user6178746

2016-04-16 04:58:50 +00:00
Commented Apr 16, 2016 at 4:58 — user6178746
– user6178746, Commented Apr 16, 2016 at 4:58

Lukasz Szozda · Accepted Answer · 2016-04-16 07:19:52Z

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, 2016 at 7:19

answered Apr 16, 2016 at 6:57

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

DaynaJuliana Over a year ago

The calc itself is like 15 lines so it looks ugly :D

Lukasz Szozda Over a year ago

@DaynaJuliana That is why LATERAL is nice. You have to place ugly code once :)

DaynaJuliana Over a year ago

this is great ! is the VALUES necessary?

Lukasz Szozda Over a year ago

@DaynaJuliana Yes, you could use function and pass arguments from main query too :)

Lukasz Szozda Over a year ago

You could place it after last JOIN. If you have multiple complex joins "combine" them in one using subquery/CTE

|

Collectives™ on Stack Overflow

PostgreSQL reusing value from long calculation in CASE statement

1 Answer 1

10 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

10 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related