postgresql - declaring variables to be used for start_time - end_time range

Question

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;

Answer 1

This is what my final solution looked like, and worked for me.

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

Yes, DO cannot return results. The docs don't make this particularly obvious, but that's what "returning void" means. DO is really only useful for state-mutating statements (as is demonstrated in the example in the docs).

Is DO required to declare variables to be used in the select? How would I go about returning the results?

SQL has no notion of variables. You can implement a regular function, or you can use a CTE in which the users would edit only values in the first table expression, which you then reference from subsequent ones, if you need it to be pure SQL.

Fiddle showing two ways of doing something like this, using CREATE FUNCTION and a CTE:

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