If-Then-Else Statements in Postgresql

IF-THEN-ELSE statements in postgresql

As stated in PostgreSQL docs here:

The SQL CASE expression is a generic conditional expression, similar to if/else statements in other programming languages.

Code snippet specifically answering your question:

SELECT field1, field2,
  CASE
    WHEN field1>0 THEN field2/field1
    ELSE 0
  END 
  AS field3
FROM test

PostgreSQL IF statement

DO
$do$
BEGIN
   IF EXISTS (SELECT FROM orders) THEN
      DELETE FROM orders;
   ELSE
      INSERT INTO orders VALUES (1,2,3);
   END IF;
END
$do$

There are no procedural elements in standard SQL. The IF statement is part of the default procedural language PL/pgSQL. You need to create a function or execute an ad-hoc statement with the DO command.

You need a semicolon (;) at the end of each statement in plpgsql (except for the final END).

You need END IF; at the end of the IF statement.

A sub-select must be surrounded by parentheses:

    IF (SELECT count(*) FROM orders) > 0 ...

Or:

    IF (SELECT count(*) > 0 FROM orders) ...

This is equivalent and much faster, though:

    IF EXISTS (SELECT FROM orders) ...

Alternative

The additional SELECT is not needed. This does the same, faster:

DO
$do$
BEGIN
   DELETE FROM orders;
   IF NOT FOUND THEN
      INSERT INTO orders VALUES (1,2,3);
   END IF;
END
$do$

Though unlikely, concurrent transactions writing to the same table may interfere. To be absolutely sure, write-lock the table in the same transaction before proceeding as demonstrated.

Select with IF statement on postgresql

In Postgres, I would recommend using filter:

select tbl.person, COUNT(distinct tbl.project)
       sum(tbl.value) filter (where tbl.stage like '%SIGNED%') as test
from my_table tbl
group by 1;

if is control flow logic. When working with queries, you want to learn how to think more as sets. So the idea is to filter the rows and add up the values after filtering.

PostgreSQL IF-THEN-ELSE control structure

IF 2 <> 0 THEN select * from users; END IF;

You cannot use PL/pgSQL statements outside plpgsql functions. And if this fragment is from plpgsql function, then it is nonsense too. You cannot directly return result of query like T-SQL does.

CREATE OR REPLACE FUNCTION test(p int)
RETURNS SETOF users AS $$
BEGIN
  IF p = 1 THEN
    RETURN QUERY SELECT * FROM users;
  END IF;
  RETURN;
END;
$$ LANGUAGE plpgsql;

When you would get some result from function, you have to use RETURN statement - plpgsql knows only function, it doesn't support procedures - so unbounded SELECT has not sense.

How to use the ELSIF statement in Postgres

I did it with the following code:

-- UPDATE houston_real_acct_1single1property
-- SET convert_market_value=
-- CASE  
--         WHEN tax_year=2005 THEN total_market_value*1.21320615034169
--         WHEN tax_year=2006 THEN total_market_value*1.17961794019934
--         WHEN tax_year=2007 THEN total_market_value*1.15884093604151
--         WHEN tax_year=2008 THEN total_market_value*1.12145267335906
--         WHEN tax_year=2009 THEN total_market_value*1.11834431349904
--         WHEN tax_year=2010 THEN total_market_value*1.0971664297633
--         WHEN tax_year=2011 THEN total_market_value*1.06256515125065
--         WHEN tax_year=2012 THEN total_market_value*1.04321957955664
--         WHEN tax_year=2013 THEN total_market_value*1.02632796014915
--         WHEN tax_year=2014 THEN total_market_value*0.998472101797389
--         WHEN tax_year=2015 THEN total_market_value
-- END;

IF ELSE in postgresql function

Your function with slight modifications (in capitals):

1. Language is plpgsql;
2. In a SQL function you simply return the result of a query. In a plpgsql function you have to use RETURN statement to return anything. As you do not want to discard the query results but return them, add RETURN QUERY before the SELECT statements.

create OR REPLACE function public.getSomething(value1 integer)
returns table (id integer, name varchar(20)) as
$$
    BEGIN

        IF value1 = 0 THEN
            RETURN QUERY select a.id, b.name from table1 a inner join table2 b on a.Something = b.Something where 
            a.column = something and b.column = something;
        END IF;

        IF value1 = 1 THEN
            RETURN QUERY select a.id, b.name from table1 a inner join table2 b on a.Something = b.Something where 
            a.column = something and b.column = something;
        END IF;

    END;
            
$$ language PLPGSQL;

How to Write IF ELSE Statements inside RETURN QUERY EXECUTE in PostgreSql

Read up about PL/pgSQL syntax as hinted in the comments.

However, you can just merge your query variants and use a plain OR for the possibly empty / null input parameter i_name:

CREATE OR REPLACE FUNCTION tds_master.a_report(
    i_entity_id integer,
    i_client_id integer,
    i_branch_id integer,
    i_name text,
    i_finyear integer)
  RETURNS TABLE(employee_name varchar(100), pan varchar(10), optfor115bac varchar(1)
              , taxable_income numeric, income_tax numeric, credit_us_87a numeric
              , surcharge numeric, education_cess numeric) 
  LANGUAGE plpgsql AS
$func$
BEGIN
   RETURN QUERY EXECUTE format(
   $q$
   SELECT ed.name -- AS employee_name
        , ed.pan
        , sd.opting_for_us115bac
        , sd.total_taxable_income
        , sd.income_tax_on_total_income
        , sd.rebate_us87a
        , sd.surcharge
        , sd.education_cess
   FROM   %1$I.saldet sd
   JOIN   %1$I.employee_deductee ed ON ed.ed_id = sd.employee_id
   WHERE  sd.entity_id = $1
   AND    sd.client_id = $2
   AND    sd.branch_id = $3       
   AND   (i_name = '' OR  i_name IS NULL OR upper(ed.name) = upper(i_name))  -- !
   $q$, 'tds' || i_finyear::text)
   USING i_entity_id, i_client_id, i_branch_id;
END
$func$;

Much simpler. Same performance.

I use format(), which is the clean approach. Granted, while you only concatenate an integer value, plain concatenation is reasonably safe.

IF else inside case when statement

We can nest CASE expressions, or use multiple tests if appropriate.

These 2 example give the same result.

select 
  x,y,
  case 
    when x = 1 then
      case when y = 1 then 11
      else 12 
      end
    when x = 2 then
      case when y = 1 then 21
      else 22
      end
  else 99 end myExression
from test;

IF NOT EXISTS (SELECT *
                      FROM mytable
                      WHERE name = 'myname') THEN
  ...
END IF;

DO
$$
BEGIN
  IF NOT EXISTS (SELECT *
                        FROM mytable
                        WHERE name = 'myname') THEN
    ...
  END IF;
END;
$$
LANGUAGE plpgsql;