在“WHERE”中或附近的语法错误

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

2 个解决方案

#1

looks like the syntax error is LEFT JOIN needs an ON clause before the WHERE

看起来语法错误是左连接在WHERE之前需要一个ON子句。

#2

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

#1