postgresql/pgAdmin -在查询运行时接受start_date和end_date参数作为输入

Question

这是我使用pgAdmin处理的postgresql。

如果这是一些常识，请原谅，我是postgresql的新手……通过之前的搜索，我没有找到任何直接的答案。

我想知道是否有一种简单的方法来实现start_time/end_time参数作为输入，当查询使用pgadmin和它的任何内置特性运行时。

我在这里使用的数据类型是"timestamp with timezone“。

寻找实现这一点的最佳方式的一些方向。

我考虑将start_time和end_time声明为变量，然后使用WHERE根据这些变量进行过滤，但是没有第三方/应用程序级别的解决方案，当查询在pgadmin中运行时，有没有办法提示输入？

我很感谢大家的建议--这是我的一些尝试，但它是错误的:查询没有结果数据的目的地。

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)
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 BETWEEN start_date AND end_date
order by account.id asc, menu.name, kitchen_item.name asc, order_item.created desc;

end $$;

Answer 1

DO创建一个不返回任何数据的匿名函数。您可以使用WITH

WITH input (start_date, end_date) AS 
(SELECT '2020-10-01'::timestamp AS start_date,
'2020-10-05'::timestamp AS end_date)
SELECT ...
FROM...
JOIN input 
WHERE orders.placed BETWEEN input.start_date AND input.end_date