Assign value of subquerry inside a CASE expression-CodePudding

I want to get a value if it exists, if it doesn't exist I want to return a random number (it's a bit more complicated than that but that's the essence of it).

My query I think would look something like this:

SELECT something, CASE
    WHEN EXISTS(SELECT column_1 FROM table_1 WHERE something) THEN column_1
    ELSE RANDOM()
END;

which is not valid obviously.

I use postgresql and I am inside a plpgsql function.

What should I do?

CodePudding user response：

I found it!

using COALESCE I can do this:

SELECT something, CΟALESCE((SELECT column_1 FROM table_1 WHERE something), RANDOM())

When the first query does not return something, RANDOM takes the lead.

CodePudding user response：

You can't reference the column from an EXISTS condition as that only yields true or false. The SELECTed columns from the subquery are completely ignored as well.

So you need to repeat the SELECT in the THEN part to get the column value:

SELECT 'something' as one_column, 
       CASE
         WHEN EXISTS (SELECT * 
                      FROM table_1 
                      WHERE <some_condition>) 
           THEN (select column_1 from table_1 where <some_condition>)
         ELSE random()
       END as other_column;

Note the parentheses around the SELECT in the THEN branch. They are necessary to mark this as a scalar query. You also need to make sure that some_condition only returns a single row