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?

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?

• postgresql
• function
• if-statement

Share Improve this question Follow edited Jan 31 at 10:18 DarkBee 15.5k88 gold badges7272 silver badges118118 bronze badges asked Jan 31 at 10:13 EducatEducat 511 bronze badge

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 2

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.