I'm relatively new to postgresql, so forgive me if this is a simple problem.
I'm trying to implement a query with start_time and end_time arguments, which the user can input their own dates into for customized results.
From taking bits and pieces from other examples online, I've come up with something that more or less runs, however I'm getting an error "query has no destination for result data".
I'm simply trying to return the data from the select statement.
Is there something trivial I'm missing here?
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$$
EDIT - This is the final result I've worked out which appears to work for this purpose utilizing temp tables.
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;