Using If else in posgreSQL function - syntax error

Question

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;

Gordon Linoff · Accepted Answer · 2021-07-13 12:25:39Z

3

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).

edited Jul 13, 2021 at 12:25

answered Jul 13, 2021 at 0:46

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

3 Comments

zeroanthem86 Over a year ago

Thank you for this! Super helpful to see where my mistakes were.

zeroanthem86 Over a year ago

How do you do nested ifs in the return query? For example if I have another variable "joined_to" and based on that I want to union a table. Can I do something like

IF group_by = 'x' THEN     return query select t1.first_name, t1.price from table1 t1 IF joined_to="a" THEN UNION ALL SELECT * from table3; END IF;

Frank Heikens Over a year ago

@zeroanthem86: This function isn't IMMUTABLE, it does a table lookup. postgresql.org/docs/current/sql-createfunction.html

Collectives™ on Stack Overflow

Using If else in posgreSQL function - syntax error

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