Pick another column value depending on column value (postgresql)

Question

In my frontend application I have a function that is called pick(VALUE,'col1','col2','col3'). If the VALUE is 2 the value in col2 should be picked.

This is very handsome for replacing long code using "case when", "switch case" or "if else" calculations.

I have tried to find a similar function in Postgres, but no luck so far. Seen some function array() and values() mentioned, but cannot find the correct syntax.

The goal is to set an return on of three column values depending on first column value.

Pseudo code (not working):

Select status values(column1,column2,column3)from code

I know I can do this by using "case-when-then-else-end", but I am looking for a shorter way to achieve the same thing.

Jsfiddle showing the principe. But I only want to pick ONE value depending on type:

http://sqlfiddle.com/#!15/e0b41/10

Are column1, column2, column3, columnN of the same type? — Eggplant
– Eggplant, Commented Jul 14, 2015 at 7:27
So you want multiple columns based on the value of code_type (one column for each type)? — user330315
– user330315, Commented Jul 14, 2015 at 8:35
No, I want ONE value based on the exact type of code. The original question was how I can pick ONE value from another column based on type. — sibert
– sibert, Commented Jul 14, 2015 at 8:40
But your SQLFiddle creates three columns, one for each code_type — user330315
– user330315, Commented Jul 14, 2015 at 9:26

krokodilko · Accepted Answer · 2015-07-14 09:26:02Z

1

You can create an array of values from pr_* columns, then pick one of them in this way:

(array[prl_1,prl_2,prl_3])[code_type]

Here is a simple demo: http://sqlfiddle.com/#!15/e0b41/23

select *,
       (array[prl_1,prl_2,prl_3])[code_type]
from code
left join prl on prl_id =1

answered Jul 14, 2015 at 9:26

krokodilko

36.3k7 gold badges62 silver badges86 bronze badges

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

6 Comments

sibert Over a year ago

Right on target, simple and straightforward. Thank you!

sibert Over a year ago

Another question: What if the code_type is zero=0. What value is picked up?

krokodilko Over a year ago

Null is returned when an index is less than 1 Or greater than a size of the array.

sibert Over a year ago

So you have to have a "case when code_type=0" to deal with code_type = 0 in together with the array?

pozs Over a year ago

@sibert from your question, the pick() function is also 1 based ('col2' is the 2. parameter after VALUE), so this would work the same. If you want to start with 0 instead, you should just add + 1 to VALUE / code_type (in both ways), like (array[...])[value + 1] or pick(value + 1, ...).

|

Collectives™ on Stack Overflow

Pick another column value depending on column value (postgresql)

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related