admin管理员组

文章数量:1417551

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 edited Jan 31 at 10:18 DarkBee 15.5k8 gold badges72 silver badges118 bronze badges asked Jan 31 at 10:13 EducatEducat 51 bronze badge
Add a comment  | 

1 Answer 1

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.

本文标签： postgresqlUsing IF in function for different queries as a resultStack Overflow

版权声明：本文标题：postgresql - Using IF in function for different queries as a result - Stack Overflow 内容由网友自发贡献，该文观点仅代表作者本人， 转载请联系作者并注明出处：http://www.betaflare.com/web/1745269559a2650806.html， 本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容，一经查实，本站将立刻删除。