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, Jul 08 '15 at 04:19

Patrick · Accepted Answer · 2015-07-08T05:24:27.650

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 08 '15 at 05:24

answered Jul 08 '15 at 03:46

Patrick

29,357
6
62
90

I don't think there is an `IFNULL` in PostgreSQL, you'd want to use the standard [`COALESCE`](http://www.postgresql.org/docs/current/static/functions-conditional.html#FUNCTIONS-COALESCE-NVL-IFNULL) or skip the conversion and go to [`IS DISTINCT FROM` or `IS NOT DISTINCT FROM`](http://www.postgresql.org/docs/current/interactive/functions-comparison.html). – mu is too short Jul 08 '15 at 05:02
Yeah. Obvious mistake. Corrected. – Patrick Jul 08 '15 at 05:24
@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. – Patrick Jul 08 '15 at 05:27

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

1 Answers1