Using If else in posgreSQL function - syntax error

user_12623819 0
Viewed 43

I am trying to create a function that will execute different SQL based on provided input to the function (i.e. IF ELSE). I am trying something like below but keep getting a syntax error. Does anyone know what I am doing wrong? Any pointers will be greatly appreciated!

CREATE or replace FUNCTION sales_price(group_by text) 
RETURNS table(first_name text, price decimal)
AS $$

  IF group_by="x" THEN
    select first_name, price from table1;
  ELSIF group_by="y" THEN
    select first_name, price from table2;
  END IF;
$$ LANGUAGE SQL IMMUTABLE;
postgresql function sql
1 Answers

You have several issues, but the biggest is sql rather than plpgsql:

CREATE or replace FUNCTION sales_price(group_by text) 
RETURNS table(first_name text, price decimal)
AS $$
BEGIN
  IF group_by = 'x' THEN
    return query select t1.first_name, t1.price from table1 t1;
  ELSIF group_by = 'y' THEN
    return query select t2.first_name, t2.price from table2 t2;
  END IF;
END;
$$ LANGUAGE plpgsql;

Here is a db<>fiddle.

I removed the IMMUTABLE because the function is not (well, I suppose you might know that the underlying tables never change).

user_11440350
Related