PL/pgSQL 中的"malformed array literal"错误

Question

我在PL/pgSQL中遇到错误：

"错误：格式错误的数组文字："2019-11-22 09：18：07.248537" 详细信息： 数组值必须以"{"或维度信息开头。上下文： PL/pgSQL 函数 chargelengtth(( 第 11 行在 FOR over SELECT 行">

CREATE OR REPLACE FUNCTION chargelengtth() RETURNS text AS
$$
DECLARE chargetime integer;
DECLARE othertime integer;
DECLARE total integer;
DECLARE charge_length text[];
DECLARE chargestatus text;
DECLARE otherstatus text;
BEGIN
    total := 0;
    chargetime := 0;
FOR charge_length IN
    SELECT * FROM table_name
LOOP
    RAISE NOTICE 'Stuff: %', charge_length;
    IF(charge_length[diagnostic] = 'Charging' AND chargetime = 0) THEN
        chargetime := extract(epoch from charge_length[time]);
    END IF;
    IF(charge_length[diagnostic] != 'Charging') THEN
        othertime := extract(epoch from charge_length[time]);
        total := total + (othertime - chargetime);
        chargetime := 0;
    END IF;
END LOOP;
RETURN to_char(total * interval '1 sec','HH24h MIm');
END$$ LANGUAGE plpgsql;
SELECT * FROM chargelengtth()`

您能否分享您的知识以解决此问题。

Answer 1

如手册中所述，当您想要遍历 SELECT 语句时，您需要使用记录变量：

CREATE OR REPLACE FUNCTION chargelengtth() 
  RETURNS text AS
$$
DECLARE 
  chargetime integer;
  othertime integer;
  total integer;
  chargestatus text;
  otherstatus text;
  l_row record; --<< here
BEGIN
  total := 0;
  chargetime := 0;
  FOR l_row IN SELECT * FROM table_name
  LOOP
      RAISE NOTICE 'Stuff: %', l_row;
      IF (l_row.diagnostic = 'Charging' AND chargetime = 0) THEN
          chargetime := extract(epoch from l_row.time);
      END IF;
      IF (l_row.diagnostic != 'Charging') THEN
          othertime := extract(epoch from l_row.time);
          total := total + (othertime - chargetime);
          chargetime := 0;
      END IF;
  END LOOP;
  RETURN to_char(total * interval '1 sec','HH24h MIm');
END$$ LANGUAGE plpgsql;
SELECT chargelengtth()

---

据我所知，您可以将其替换为单个 SELECT 语句。假设time列是timestamp，我认为以下内容等效于您正在执行的操作(除了末尾interval的格式(：

with ct (chargetime) as (
  select time 
  from table_name
  where diagnostic = 'Charging'
  limit 1
)
select sum(t.time - ct.chargetime) 
from the_table t
  cross join ct
where t.diagnostic <> 'Charging'

chargetime的检索有点令人困惑，因为您似乎依赖于行的某种顺序，但您的查询没有order by。没有order by的limit 1基本上是相同的，但是如果你想要可重现的结果，你应该真正指定一个order by(也为你的FOR循环语句(