Postgresql – How to use WITH inside IF statement in PostgreSQL

plpgsqlpostgresql

I am writing a function that accepts a text flag based on which I need to run some code. So I am using an IF clause. However the code that has to execute inside IF and ELSE is a recursive CTE which has different joins for the different text flags. Following is sample that I am trying to write but getting an error. I tried writing the code that starts with WITH and applying the IF with the final SELECT statement but it also gave the same error which is "syntax error at or near "IF"".

How can I effectively use IF in this function?

CREATE OR REPLACE FUNCTION public.get_something(myTextFlag VARCHAR(20)) RETURNS TABLE 
(
rNum BIGINT,
Colmn1 Text
)
AS $body$

IF (myTextFlag = 'FirstPart') THEN
   WITH recursive cte_1 AS
(
code with table 1 and 2 and cte_1
)
Select some_columns from cte_1 and table 10, 11 and 12;

ELSE

   WITH recursive cte_2 AS
(
code with table 1,2,3 and 4 and cte_2
)
Select * from cte_2 and table 10, 11, 13, 14;

END IF;

$body$
LANGUAGE SQL
SECURITY DEFINER
 STABLE;

Best Answer

IF does not exist in the SQL language (which is why you get a syntax error) but it's available in plpgsql, the procedural language.

With plpgsql, you could write something like

CREATE OR REPLACE FUNCTION public.get_something(myTextFlag VARCHAR(20)) RETURNS TABLE 
(
rNum BIGINT,
Colmn1 Text
)
AS $body$
BEGIN
IF (myTextFlag = 'FirstPart') THEN
  RETURN QUERY <insert your 1st query here>
ELSE
  RETURN QUERY <insert your 2nd query here>
END IF;
END
$body$
LANGUAGE plpgsql
SECURITY DEFINER
 STABLE;

Your 1st and 2nd queries must return the same structure that also must match the function definition, but aside from that, they can be completely different inside.

Related Question