postgresql “WHERE”处或附近的语法错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27654911/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Syntax error at or near "WHERE"
提问by Parth
While creating postgres function it is resulting an error as
在创建 postgres 函数时，它会导致错误为
ERROR: syntax error at or near "WHERE" LINE 19: WHERE s.shift_id = shiftid ^ ********** Error **********
错误：“WHERE”第 19 行或附近的语法错误：WHERE s.shift_id = shiftid ^ ********** 错误 **********
ERROR: syntax error at or near "WHERE" SQL state: 42601 Character: 108
错误：“WHERE”处或附近的语法错误 SQL 状态：42601 字符：108
Please Help..
请帮忙..
CREATE OR REPLACE FUNCTION shiftwisedata_sp(INOut shiftid bigint,InOut userdate date,OUT shift_name character varying (50),OUT from_time character varying(50),OUT to_time character varying(50),OUT cal bigint)
RETURNS SETOF record AS
$BODY$
BEGIN
return query
SELECT userdate, s.shift_name,
('00:00' + (h.hour * interval '1Hour'):: time) AS from_time,
('00:00' + ((h.hour + 1) * interval '1Hour'):: time) AS to_time,
COALESCE(r.Readings, 0) AS readings
FROM shift_wise s
CROSS JOIN (VALUES(0), (1), (2), (3), (4), (5), (6), (7), (8), (9),
(10), (11), (12), (13), (14), (15), (16), (17), (18), (19),
(20), (21), (22), (23)) AS h(hour)
LEFT JOIN LATERAL (SELECT SUM(r.param_value) AS Readings
FROM table_1 r
WHERE r.timestamp_col >= CAST(userdate as timestamp without time zone ) + h.hour * interval '1Hour'
AND r.timestamp_col < CAST(userdate as timestamp without time zone ) + h.hour + 1 * interval '1Hour'
) AS r
WHERE s.shift_id = shiftid
AND (s.to_time > s.from_time AND
h.hour >= date_part(HOUR, s.from_time) AND
h.hour < date_part(HOUR, s.to_time)
OR
s.to_time < s.from_time AND
(h.hour >= date_part(HOUR, s.from_time) OR
h.hour < date_part(HOUR, s.to_time))
)
ORDER BY s.to_time;
END;
$BODY$
LANGUAGE plpgsql VOLATILE回答by Jasen
looks like the syntax error is LEFT JOIN needs an ON clause before the WHERE
看起来语法错误是 LEFT JOIN 在 WHERE 之前需要一个 ON 子句
回答by Parth
Working Postgres Function
工作 Postgres 函数
CREATE OR REPLACE FUNCTION shiftwisedata_sp(IN shiftid bigint, INOUT userdate date, OUT shift_name character varying, OUT from_time time without time zone, OUT to_time time without time zone, OUT readings bigint)
RETURNS SETOF record AS
$BODY$
BEGIN
return query
SELECT userdate, s.shift_name,
('00:00' + (h.hour * interval '1Hour'):: time) AS from_time,
('00:00' + ((h.hour + 1) * interval '1Hour'):: time) AS to_time,
COALESCE(r.Readings, 0) AS readings
FROM shift_wise s
CROSS JOIN (VALUES(0), (1), (2), (3), (4), (5), (6), (7), (8), (9),
(10), (11), (12), (13), (14), (15), (16), (17), (18), (19),
(20), (21), (22), (23)) AS h(hour)
LEFT JOIN LATERAL (SELECT CAST(SUM(r.param_value) as bigint) AS Readings
FROM table_1 r
WHERE r.timestamp_col >= (CASt(userdate As timestamp without time zone) + h.hour * interval '1Hour')
AND r.timestamp_col < (CASt(userdate As timestamp without time zone) + (h.hour + 1) * interval '1Hour')
) AS r ON TRUE
WHERE s.shift_id = shiftid
AND (s.to_time > s.from_time AND
h.hour >= Extract(HOUR from CAST(s.from_time as time)) AND
h.hour < Extract(HOUR from CAST(s.to_time as time))
OR
s.to_time < s.from_time AND
(h.hour >= Extract(HOUR from CAST(s.from_time as time)) OR
h.hour < Extract(HOUR from CAST(s.to_time as time))
))
ORDER BY s.to_time;
END;
$BODY$
LANGUAGE plpgsql VOLATILE