How to make a function use CASE and IFNULL in postgreSQL?

Question

can you tell me how to use CASE and IFNULL in postgre? i'm migrating from sql to postgreSQL, and i want to make a function with this syntax :

SELECT a.furniture_id, a.furniture_brand, a.furniture_cost, 
CASE 
  WHEN furniture_id = f_id THEN a.furniture_content 
  ELSE '' 
END CONTENT 
   FROM tb_furniture a
   WHERE 
    (IFNULL(a.sale_id,0) = IFNULL(f_id,0) OR (a.furniture_id = f_Id AND IFNULL(a.furniture_content,'') > '')) 
    AND a.published = 1;

thanks before(y)

Er, PostgreSQL is SQL. Do you mean "I'm migrating from Microsoft SQL Server to PostgreSQL"? — Craig Ringer
– Craig Ringer, Commented Jul 8, 2015 at 4:19

Patrick · Accepted Answer · 2015-07-08 05:24:27Z

3

This seems to be what you are after, but carefully check the WHERE conditions:

SELECT a.furniture_id, a.furniture_brand, a.furniture_cost, 
       CASE WHEN a.furniture_id = f_id
       THEN a.furniture_content 
       ELSE '' 
       END AS content
FROM tb_furniture a
WHERE (coalesce(a.sale_id,0) = coalesce(f_id,0) OR length(content) > 0)
  AND a.published = 1;

edited Jul 8, 2015 at 5:24

answered Jul 8, 2015 at 3:46

Patrick

33k7 gold badges73 silver badges102 bronze badges

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

3 Comments

mu is too short Over a year ago

I don't think there is an IFNULL in PostgreSQL, you'd want to use the standard COALESCE or skip the conversion and go to IS DISTINCT FROM or IS NOT DISTINCT FROM.

Patrick Over a year ago

Yeah. Obvious mistake. Corrected.

Patrick Over a year ago

@AnitaWulandari Since f_id is a parameter and used both in the CASE statement and in the WHERE condition, the latter can be simplified. See updated answer.

Collectives™ on Stack Overflow

How to make a function use CASE and IFNULL in postgreSQL?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related