postgresqlfunctionif-statement

Using IF in function for different queries as a result

I need to create a PostgreSQL function that will execute a query depending on the input value.

Like this on

FUNCTION public.get_data( in_param smallint)
...

BEGIN

WITH cte1 as ( 
  select * from tbl1),

  IF in_param=1 THEN
  cte2 as (
  select * from tbl2
  where tbk2.col1 > 5 
  )
  ELSE 
  cte2 as (
  select * from tbl2
  where tbl2.col3 < 0 
  )
  END IF


  select * from cte1, cte2
  ...

END

How I can do it?

Solution

  • A case expression in the where clause rather than an if statement will do the job.

    WITH cte1 as 
( 
  select * from tbl1
),
cte2 as 
(
  select * from tbl2
  where case when in_param = 1 then (col1 > 5) else (col3 < 0) end
)
...

    Unrelated but cte1 is trivial and equivalent to tbl1 therefore unnecessary.