我想写一个函数,返回一个表,表中的所有行在 firstDate
以及 lastDate
. 行具有不带时区的数据类型timestamp它们还必须具有特定的节点id。
这是我的职责:
CREATE OR REPLACE FUNCTION get_measurements_by_node_and_date(nodeID INTEGER, firstDate date, lastDate date)
RETURNS TABLE (measurement_id INTEGER, node_id INTEGER, carbon_dioxide DOUBLE PRECISION,
hydrocarbons DOUBLE PRECISION, temperature DOUBLE PRECISION,
humidity DOUBLE PRECISION,
air_pressure DOUBLE PRECISION,
measurement_timestamp timestamp without time zone ) AS
$$
DECLARE
sql_to_execute TEXT;
BEGIN
SELECT 'SELECT measurements_lora.id,
measurements_lora.node_id,
measurements_lora.carbon_dioxide,
measurements_lora.hydrocarbons,
measurements_lora.temperature,
measurements_lora.humidity,
measurements_lora.air_pressure,
measurements_lora.measurement_timestamp AS measure
FROM public.measurements_lora
WHERE measurements_lora.measurement_timestamp <= '||lastDate||'
AND measurements_lora.measurement_timestamp >= '||firstDate||'
AND measurements_lora.node_id = '||nodeID||' '
INTO sql_to_execute;
RETURN QUERY EXECUTE sql_to_execute;
END
$$ LANGUAGE plpgsql;
列测量时间戳是不带时区的时间戳类型,格式为yy-mm-dd hh-mm-ss
当我跑的时候 SELECT * FROM get_measurements_by_node_and_date(1, '2020-5-1', '2020-5-24')
我得到以下错误:
ERROR: operator does not exist: timestamp without time zone <= integer
LINE 10: ... WHERE measurements_lora.measurement_timestamp <= 2020-05...
我不明白为什么它说“整数”,因为我定义得很清楚 firstDate
以及 lastDate
作为类型 date
.
2条答案
按热度按时间oknwwptz1#
帕维尔说的。
此外,没有任何迹象表明需要从pl/pgsql开始。平原
SELECT
我能做到。或者一个sql函数,如果您想将其持久化到数据库中。请参见:postgresql函数中sql语言与plpgsql语言的区别
关于:
所有的行间
firstDate
以及lastDate
精确定义包含/排除的上限/下限,以避免意外的角大小写结果。在比较timestamp
列到adate
,后者被强制为表示当天第一个示例的时间戳:YYYY.MM.DD 00:00:00
.你的问题是:
... 包括所有
firstDate
,但排除所有lastDate
除了00:00的第一个(公共)示例。通常不是你想要的。根据你的公式,我想这就是你真正想要的:① 这包括所有
lastDate
,而且效率很高。你只需要加/减一个integer
值到/来自date
加减天数。显式强制转换为::timestamp
是可选的,因为日期将在表达式中自动强制。但既然我们正在努力消除混乱。。。相关:
如何使用postgresql确定上个月的最后一天?
旁白1:
列
measurement_timestamp
属于类型timestamp without time zone
格式为yy-mm-dd hh-mm-ss不。
timestamp
值未格式化,句点。它们只是时间戳值(内部存储为自epoch以来的微秒数)。显示完全独立于值,可以在不改变值的情况下以100多种方式进行调整。摆脱这种误解,以便更好地理解发生了什么。请参见:在rails和postgresql中完全忽略时区
旁白2:
关于sql的狡猾本质
BETWEEN
:如何在时间戳列中添加日/夜指示器?
旁白3:
在postgres中考虑合法的小写标识符。
first_date
而不是firstDate
. 请参见:postgresql列名是否区分大小写?
相关:
返回数据立方体的postgresql函数
postgresql试图在函数中使用execute format,但在coalesce中提供字符串格式时,get column not found出错
mrwjdhj32#
在这种情况下,最好先编写已执行的查询。我尽量减少你的例子:
我收到了同样的错误信息。所以有不止一个错误:
动态查询无效-where子句如下所示
where current_timestamp <= 2020-05-25
你可以看到,这是无效的-没有引号。当您手动使用引号时,您可以修复它(但这是一个很强的错误,不要这样做,或者您可以使用函数)quote_literal
就像where current_timestamp <= ' || quote_literal(d)
.现在生成的查询是正确的:
但在这种情况下,使用
EXECUTE USING
. 当变量用作查询参数(而不是表或列名)时,可以使用USING
条款。那么你就不需要引用:但最大的错误是在不必要时使用动态sql(如您的示例)。没有任何明显的理由说明你为什么使用
RETURN QUERY EXECUTE
声明。你可以只用RETURN QUERY
: