How to use WITH inside of postgres if statement?
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 a 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;
Postgres IF statement 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
ELSE
RETURN QUERY
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.