0

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;
3
  • @AdamKG thank you for your input. Would you mind having a look at my edit, and the revised query I posted... I believe I've accomplished what I'm trying to do. To your knowledge, is there anything wrong or incorrect with the 'solution' I found? It seems to work... is this what you were suggesting? Thanks – boog Oct 7 '20 at 20:52
  • 1
    Rather than editing the question and adding the answer there, you should write an answer for your own question. That is less confusing and works best for a Q&A forum. – Laurenz Albe Oct 8 '20 at 2:36
  • @LaurenzAlbe done, thanks! – boog Oct 8 '20 at 12:53
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;
1
  • If it the question boils down to how to run a query with two parameters, neither a DO block nor a temp table are needed. In your current code, the values for start/end_date are hardcoded in the DO block, so you might just as well hardcode them in a query. – Daniel Vérité Oct 9 '20 at 15:20
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.