将用于start_time - end_time范围的声明变量

Question

我对postgresql比较陌生，所以如果这是一个简单的问题，请原谅我。

我试图用start_time和end_time参数实现一个查询，用户可以将它们自己的日期输入到定制结果中。

通过从其他示例中获取一些零碎的内容，我想出了一些或多或少运行的内容，但是我得到了一个错误：“查询没有结果数据的目的地”。

我只是试图从select语句返回数据。

这里有什么琐碎的东西吗？

do $
DECLARE
    start_date timestamp := '2020-10-1';
    end_date timestamp := '2020-10-5';
begin

select distinct on (account.id, menu.name, kitchen_item.name)
...
query stuff
....
where orders.placed BETWEEN start_date AND end_date
order by account.id asc, menu.name, kitchen_item.name asc, order_item.created desc;

end$

编辑--这是我想出的最终结果，它似乎是为了达到这个目的，使用临时表。

DO $
DECLARE 

--Specify start date - end date in YYYY-MM-DD format.
start_date timestamp := '2020-10-1';
end_date timestamp := '2020-10-5';


BEGIN
    CREATE TEMP TABLE temp_output ON COMMIT DROP AS
    select distinct on (account.id, menu.name, kitchen_item.name)
    account.id as "Account ID",
    account.firstname as "Seller First Name", 
    account.lastname as "Seller Last Name",
    account.email as "Seller Email",
    account.phone as "Seller Phone",
    address.address as "Seller Address (Street)",
    address.address_2 as "Seller Address 2",
    account.zip_code as "Seller Zip",
    address.neighborhood as "Seller Neighborhood",
    menu.name as "Name of active menu",
    kitchen_item.name as "Dishes", 
    kitchen_item.price as "Price",
    kitchen_item.daily_max_orders as "Quantity",
    menu.pickup_start_time as "Start time", 
    menu.pickup_end_time as "End time",
    menu.repeat_mon as "Monday",
    menu.repeat_tues as "Tuesday",
    menu.repeat_wed as "Wednesday",
    menu.repeat_thurs as "Thursday",
    menu.repeat_fri as "Friday",
    menu.repeat_sat as "Saturday", 
    menu.repeat_sun as "Sunday",
    order_item.created as "Date of last sale"
    from account
    left join store on account.id = store.account_id
    left join menu on store.id = menu.store_id
    left join menu_item on menu.id = menu_item.menu_id
    left join kitchen_item on (menu_item.kitchen_item_id = kitchen_item.id and store.id = kitchen_item.store_id)
    left join orders on (orders.store_id = store.id)
    left join order_item on (order_item.order_id = orders.id)
    join store_address on store.id = store_address.store_id
    join address on store_address.address_id = address.id
    where orders.placed >= start_date AND orders.placed <= end_date
    order by account.id asc, menu.name, kitchen_item.name asc, order_item.created desc;
END $;

SELECT * FROM temp_output;

Answer 1

这是我最后的解决方案，并为我工作。

DO $
DECLARE 

--Specify start date - end date in YYYY-MM-DD format.
start_date timestamp := '2020-10-1';
end_date timestamp := '2020-10-5';


BEGIN
    CREATE TEMP TABLE temp_output ON COMMIT DROP AS
    select distinct on (account.id, menu.name, kitchen_item.name)
    account.id as "Account ID",
    account.firstname as "Seller First Name", 
    account.lastname as "Seller Last Name",
    account.email as "Seller Email",
    account.phone as "Seller Phone",
    address.address as "Seller Address (Street)",
    address.address_2 as "Seller Address 2",
    account.zip_code as "Seller Zip",
    address.neighborhood as "Seller Neighborhood",
    menu.name as "Name of active menu",
    kitchen_item.name as "Dishes", 
    kitchen_item.price as "Price",
    kitchen_item.daily_max_orders as "Quantity",
    menu.pickup_start_time as "Start time", 
    menu.pickup_end_time as "End time",
    menu.repeat_mon as "Monday",
    menu.repeat_tues as "Tuesday",
    menu.repeat_wed as "Wednesday",
    menu.repeat_thurs as "Thursday",
    menu.repeat_fri as "Friday",
    menu.repeat_sat as "Saturday", 
    menu.repeat_sun as "Sunday",
    order_item.created as "Date of last sale"
    from account
    left join store on account.id = store.account_id
    left join menu on store.id = menu.store_id
    left join menu_item on menu.id = menu_item.menu_id
    left join kitchen_item on (menu_item.kitchen_item_id = kitchen_item.id and store.id = kitchen_item.store_id)
    left join orders on (orders.store_id = store.id)
    left join order_item on (order_item.order_id = orders.id)
    join store_address on store.id = store_address.store_id
    join address on store_address.address_id = address.id
    where orders.placed >= start_date AND orders.placed <= end_date
    order by account.id asc, menu.name, kitchen_item.name asc, order_item.created desc;
END $;

SELECT * FROM temp_output;

Answer 2

是的，DO不能返回结果。医生们没有特别清楚地说明这一点，但这就是“返回空”的意思。DO实际上只对状态变异语句有用(如文档中的示例所示)。

是否需要声明在select中使用的变量？我怎么才能把结果还给你呢？

SQL没有变量的概念。您可以实现正则函数，也可以使用CTE，其中用户只编辑第一个表表达式中的值，然后从后续表表达式中引用值，如果需要使用纯SQL。

“小提琴”展示了两种使用CREATE FUNCTION和CTE进行类似操作的方法：

https://dbfiddle.uk/?rdbms=postgres_13&fiddle=7e340339a61ed22ec43666be269601ea