PostgreSQL PL / pgSQL: запрос, хранящийся в таблице (часы работы)
У меня есть приложение (postgresql 9.6 переносится на 10), в котором я хотел бы получать результаты в таблице И в одно и то же время соответствовать часам работы, хранящимся в этой таблице.
Поясним на вымышленном примере: У меня есть таблица магазинов:
store_name | opening_hours
-----------------+-----------------------------
storeA | ((wday between 1 and 5) and (hour between 10 and 20))
storeB | ((wday between 2 and 5) and (hour between 9 and 18)) OR (wday in (6,7) and (hour between 9 and 12))
Я хотел бы запросить эту таблицу и получить открытое хранилище с момента запроса (не беспокоясь о часовом поясе). (для кого не все равно: в моей стране 1-й день недели - понедельник, но в этом примере нам все равно):
- Если мой запрос запрашивает открытые магазины в среду, 19 часов (7 вечера), он вернет только storeA.
- Если запрос запускается в полночь, ни один не будет выбран
- если запрос произошел в четверг 11ч (11:00), будут выбраны оба магазина ....
Вы можете помочь мне заставить эту мелочь работать? Думаю, мне просто не хватает правильного способа написать это.
Обновлено: «час открытия» - это всего лишь вещь, чтобы задокументировать способ решения этой проблемы. Ни в коем случае, я добавлю несколько новых таблиц в эту базу данных. Единственный найденный здесь ответ - это способ оценки выражений, хранящихся в таблицах.
Да, именно так написано в таблице. Но я открыт для любого простейшего способа сделать это ...
Возможный дубликат Лучший способ хранить рабочее время и эффективно запрашивать его
Ответы 2
Поскольку вы открыты для предложений, я предлагаю посмотреть принятый ответ на этот вопрос: Лучший способ хранить рабочее время и эффективно запрашивать его
Вашей текущей структурой таблицы будет довольно сложно управлять. Если вы измените структуру таблицы, чтобы она соответствовала принятому ответу, приведенному выше, это именно то, что вам нужно, и это упростит запрос.
РЕДАКТИРОВАТЬ: Для полноты предлагаемая структура таблицы в ссылке:
Чтобы сохранить часы нормальной работы, вам необходимо сохранить несколько записей, содержащих:
- Магазин - INTEGER
- DayOfWeek - INTEGER (0–6)
- OpenTime - ВРЕМЯ
- CloseTime - ВРЕМЯ
Я оставлю это на потом, если не найду более умного способа сделать это, но я совершенно уверен, что у postgresql есть умный механизм, позволяющий сделать это элегантным способом.
@urbain Достаточно честно, вы вполне можете найти способ, который я не рассматривал, но я не думаю, что вы получите ответ более простой или полезный, чем этот.
Не полностью удовлетворен моим ответом ниже, но он работает так, как я хочу, а не с использованием простых технологий mysql. Моя работа ниже основана на Как выполнить строковый результат хранимой процедуры в postgres.
Если это может помочь, вот оно:
-- push message to debug, to 'RAISE' usefull things
SET client_min_messages TO DEBUG;
\set VERBOSITY terse
-- must return a SETOF to evaluate my test (see RETURN QUERY EXECUTE below)
-- so here is a dirty simple [temporary] table.
CREATE TEMP TABLE stupid_bool_table (opened BOOLEAN);
INSERT INTO stupid_bool_table VALUES (true),(false);
CREATE OR REPLACE FUNCTION grab_worker_test_opening_hour(shopNametext)
RETURNS SETOF stupid_bool_table AS
$BODY$
DECLARE
-- $Id: batch_workers.psql,v 1.15 2018/07/25 08:08:49 calyopea Exp $
openhour text;
BEGIN
--TODO: materialized view refreshed each hours or halfs OR clever query
SELECT INTO openhour description
FROM shop_flat_table
WHERE shop_id IN (select id from workers where shop=shopName)
AND flat_txt='openhour';
IF ( NOT FOUND ) THEN
RAISE DEBUG 'opening_hour for % is null',shopName;
RETURN QUERY EXECUTE 'SELECT opened FROM stupid_bool_table WHERE opened=true'; -- by DEFAULT
-- RAISE EXCEPTION 'cant be here'; -- could be !
ELSE
RAISE DEBUG 'opening_hour for % is % (before replace)',shopName,openhour;
openhour:=REPLACE(openhour,'dow', extract(dow from NOW())::text);
openhour:=REPLACE(openhour,'hour',extract(hour from NOW())::text);
RAISE DEBUG 'opening_hour for % is % (after replace)',shopName,openhour;
RETURN QUERY EXECUTE 'SELECT opened FROM stupid_bool_table WHERE opened=' || openhour;
END IF;
END;
$BODY$
LANGUAGE plpgsql IMMUTABLE
COST 100;
А сейчас: с данными:
shop | opening_hours
------+------------------------------------------------------
ShopA | ((dow between 1 and 5) and (hour between 9 and 16)))
ShowB | ((dow between 1 and 5) and (hour between 9 and 17)))
SELECT * FROM grab_worker_test_opening_hour('ShopB');
psql:batch_workers.psql:124: DEBUG: opening_hour for ShopB is ((dow between 1 and 5) and (hour between 9 and 17)) OR (dow in (6,7)) (before replace)
psql:batch_workers.psql:124: DEBUG: opening_hour for ShopB is ((3 between 1 and 5) and (17 between 9 and 17)) OR (3 in (6,7)) (after replace)
opened
--------
t
(1 ligne)
(и одновременно открыл = f для shopA: 2018-07-25 17:15:00 (время iso)).
Это то, как на самом деле хранятся ваши данные? Строка с данными «((wday от 1 до 5) и (час от 10 до 20))» или вы пытаетесь привести пример?