“WHERE”附近有语法错误。

Question

在创建PostgreSQL函数时，出现了以下错误：

ERROR: syntax error at or near "WHERE" LINE 19: WHERE s.shift_id = shiftid ^ ********** Error **********

ERROR: syntax error at or near "WHERE" SQL state: 42601 Character: 108

请帮忙解决。

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

- PDTech

---

查看 generate_series 内置函数。 - Jasen

1. 实际上，generate_series()可以取代values()列表。 2）日期/时间操作也应该被审查，我认为。 3）h.hour看起来很丑，但这可能是数据建模的问题。 4）也许间隔和重叠也能有所帮助。

- wildplasser

Answer 1

看起来语法错误是 LEFT JOIN 在 WHERE 之前需要一个 ON 子句

- Jasen

---

我正在将SQL存储过程转换为Postgres，在SQL SP中，我使用了OUTER apply，那么在Postgres中的等效语句是什么呢？实际上，在连接中使用ON子句用于相同列，并且在我的情况下没有这样的相同列。 - PDTech

我以前从未使用过LATERAL，但是通过检查查询语句，我认为在有问题的“WHERE”之前添加“ON TRUE”看起来像是你要寻找的解决方法。 - Jasen

Answer 2

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

- PDTech