PostgreSQL PL/pgSQL :表中存储的查询(开放时间)
PostgreSQL PL/pgSQL :表中存储的查询(开放时间)
提问于 2018-07-23 14:11:28
我有一个应用程序(PostgreSQL9.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))我想查询这个表，并从查询时起抓取打开的商店(没有时区干扰)。(谁在乎谁:在我的国家，每周的第一天是星期一，但在这个例子中我们不关心)：
- 如果我的查询要求在周三19小时(晚上7点)开设商店，它将只返回storeA。
- 如果查询在午夜启动，则不会选择任何查询。
- 如果查询发生在周四11小时(上午11点)，两个商店都将被选中.
你能帮我把这个小东西修好吗？我想我只是错过了正确的写法。
编辑：“开放时间”只是用来记录我想要解决这个问题的方式。无论如何，我都不会在这个数据库中添加一些新表。这里搜索的唯一答案是计算存储在表中的表达式的方法。
回答 2
Stack Overflow用户
回答已采纳
发布于 2018-07-25 15:25:18
我对下面的答案并不完全满意，但它的工作方式是我想要的，而不是mysql的低科技方式。我下面的工作是基于How to execute a string result of a stored procedure in 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)(同时opened=f for shopA : 2018-07-25 17:15 (iso time))
Stack Overflow用户
发布于 2018-07-23 14:29:51
由于您对建议持开放态度，我建议您看看这个问题的公认答案：Best way to store working hours and query it efficiently
您当前的表结构将很难管理。如果您修改了表的结构以匹配上面所接受的答案，这正是您所需要的，并且会使查询变得非常简单。
编辑：为了完整性，链接中建议的表结构是：
要存储正常的操作时间，您需要存储一些记录，其中包括：
- 商店整数
- DayOfWeek -整数(0-6)
- OpenTime -时间
- CloseTime -时间
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/51480859
复制相关文章
相似问题
腾讯云开发者
