IF ELSE in postgresql function

Question

I am trying to create a function which is as follows

create function public.getSomething(
        value1 integer
    )
      returns table (
             id integer,
             name varchar(20)
    
              ) as
    $$
    BEGIN
    
        IF value1 = 0 THEN
        
            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
        
            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 SQL;

But when I am trying to Create, I am getting the following error

ERROR:  syntax error at or near "if"

I tried changing the Language from SQL to plpgsql. But this time I am getting

Error: query has no destination for result data
Hint: if you want to discard the results of a select use perform instead

then I replaced the Select with Perform. This time the function is created but when I am calling it There is no result!!

What am I doing wrong? Is there any alternative? I am Using postgres version 12.3

`if .. else` can only be used in PL/pgSQL, not in SQL. In PL/pgSQL, you need `return query select ..` https://www.postgresql.org/docs/current/plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RETURNING — , Dec 01 '20 at 12:24

Stefanov.sm · Accepted Answer · 2020-12-01T14:53:48.917

Your function with slight modifications (in capitals):

Language is plpgsql;
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;

IF ELSE in postgresql function

1 Answers1