无法在PostgreSQL查询中使用参数

igetnqfo 于 2022-12-12 发布在 PostgreSQL

关注(0)|答案(1)|浏览(117)

I'm trying to simplify my databricks notebook by creating date parameters. I want my code to run with date filters, and my goal is to only have to modify one line to get my results by a period.
This is my query :

create or replace temp view deldet as
select 
  case when
    dyh_date_event::date between '2022-09-01' and '2022-09-31'
      then concat('2022-09-01','-','2022-09-31')
    else ('2022-10-01','-','2022-10-31')
  end as periode, 
  *
from
  hivestore.f_delivery_detail
where 
  dyh_date_event::date between '2022-09-01' and '2022-10-31';

This is what i tried :

set p1d1 = '2022-09-01';
set p1d2 = '2022-09-31';
set p2d1 = '2022-10-01';
set p2d2 = '2022-10-31';

create or replace temp view deldet as
select 
  case when
    dyh_date_event::date between p1d1 and p1d2
      then concat(p1d1,'-',p1d2)
    else (p2d1,'-',p2d2)
  end as periode, 
  *
from
  hivestore.f_delivery_detail
where 
  dyh_date_event::date between p1d1 and p2d2;

But it doesn't work :
AnalysisException: Column 'p1d1' does not exist

postgresql

来源：https://stackoverflow.com/questions/74742340/cannot-use-a-parameter-in-my-postgresql-query

1条答案

按热度按时间

flvlnr441#

视图不能有参数。
您可以将查询放入SQL函数中，然后将参数传递给该函数：

create or replace function deldet(p1d1 date, p1d2 date, p2d1 date, p2d2 date)
as
$$
select 
  case when
    dyh_date_event::date between p1d1 and p1d2
      then concat(p1d1,'-',p1d2)
    else (p2d1,'-',p2d2)
  end as periode, 
  *
from
  hivestore.f_delivery_detail
where 
  dyh_date_event::date between p1d1 and p2d2;
$$
language sql
stable;

然后您可以这样使用它：

select *
from deldet('2022-09-01', '2022-09-30', '2022-10-01', '2022-10-31');

赞(0）回复(0）举报 2022-12-12

我来回答

无法在PostgreSQL查询中使用参数

1条答案

相关问题

热门标签

最新问答