How to use WITH inside IF statement in PostgreSQL

Question

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;

• Akina: Where is BEGIN-END? It may be skipped only when the function body is one statement.

Answer 1

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.

• Sushant: Actually I had tried with PLPGSQL and with BEGIN and END as well but it didnt work. The 1st and 2nd queries are returning the same structure as i have to return a table structure. I made this working by adding "Return Query" just before WITH clause on each section. I am modifying the question to add this

Answer 2

I modified the code as below to make this work... Added PLPGSQL as language as Daniel suggested and added BEGIN and END as Daniel and Akina both suggested. But RETURN QUERY did the trick which I added before the WITH clause.

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 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
 RETURN QUERY 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;
END
$body$
LANGUAGE PLPGSQL
SECURITY DEFINER
 STABLE;