CREATE OR REPLACE FUNCTION nosql_data_sale(date_from VARCHAR(10), date_to VARCHAR(10))
RETURNS TABLE(place_id int, dish_id int, quantity int, total_price int)
AS $$

BEGIN

RETURN QUERY

--DECLARE date_from VARCHAR;
--DECLARE date_to VARCHAR;
--SET date_from = '2019-11-01';
--SET date_to = '2019-11-10';

select
    (receipt.data->>'place_id')::INT as place_id,
    (receipt_item.data->>'dish_id')::INT as dish_id,
    --receipt_item.data->>'dish_name' as dish_name,
    --receipt_item.data->>'is_to_set' as is_set,
		--receipt_item.data->>'set_id' as set_id,
    SUM((receipt_item.data->>'quantity')::INT) as quantity,
    SUM(((receipt_item.data->>'total_price')::INT / 100.)) as total_price
		--(receipt.data->>'date')::DATE as transation_date,
    --to_char((receipt.data->>'date')::DATE, 'MM') as transationmonth,
		--to_char((receipt.data->>'date')::DATE, 'YYYY') as transationyear,
		--(receipt.data ->> 'sys_number'::text) AS recipe_system_number,
    --(receipt.data->>'date')::TEXT AS TransactionTimestamp
from pos_receipts_nosql receipt join
    (
    select
        jsonb_array_elements(data->'receipts_items') as data
    from
        pos_receipts_nosql
    where
        (data->>'date')::DATE BETWEEN '2019-11-01' and '2019-11-10'
                AND jsonb_typeof(data->'receipts_items') = 'array'
) receipt_item on receipt.data->>'sys_number' = receipt_item.data->>'receipt_sys_number'
WHERE --to_char((receipt.data->>'date')::DATE, 'DD') = '09'
            --AND (receipt_item.data->>'dish_id')::INT IN (553)
            --AND receipt_item.data->>'is_to_set' = 'false'

				receipt_item.data->>'is_part_of_combination' = 'false'
            --AND (receipt.data->>'place_id')::INT = 33
GROUP BY place_id, dish_id; --dish_name, is_set, set_id,
		--transation_date,
    --TransactionDay,
    --transationmonth,
    --transationyear,
		--TransactionTimestamp,
		--recipe_system_number
--order by transationyear, transationmonth DESC, place_id

END;
$$
LANGUAGE plpgsql;