sql - 创建 Postgres 函数时出现语法错误

Question

我正在尝试创建一个函数，将所有一个查询值的结果相加，并将其与另一个简单查询的数量进行比较。

这就是我所拥有的，但是我在开始(第 2 行)附近遇到语法错误:

CREATE FUNCTION trigf1(sbno integer, scid numeric(4,0)) RETURNS integer
BEGIN
    declare sum int default 0;
    declare max as SELECT totvoters FROM ballotbox WHERE cid=scid AND bno=sbno;

    for r as
        SELECT nofvotes FROM votes WHERE cid=scid AND bno=sbno;
    do
        set sum = sum + r.nofvotes;
    end for

    if sum > max
        then return(0);
    else
        return(1);
END

这导致:

Syntax error near 'BEGIN'

我正在使用 postgreSQL 和 pgadminIII(以防万一)。

我不知道为什么会出现此错误，一切似乎都与教科书定义的完全一致。 (这是我正在使用的教科书:http://digilib.usu.ac.id/buku/107859/Database-systems-concepts,-6th-ed.html)

Answer 1

我不知道你用的是哪本“教科书”，但如果你写的一切都和那本书完全一样，那本书就完全错了:

CREATE FUNCTION trigf1(sbno integer, scid numeric(4,0)) 
    RETURNS integer
AS         -- error #1: no AS keyword
$body$     -- error #2: use dollar quoting to specify the function body as a string
DECLARE    -- error #3: the declare block comes before the actual code
   sum_ integer := 0; -- error #5: you can't use a reserved keyword as a variable
   max_ integer;      -- error #6:  you can't initialize a variable with a select,
   r   record;   -- you need to declare the record for the cursor loop
BEGIN
   select totvoters
     into max_
   from ballotbox 
   WHERE cid=scid AND bno=sbno;

    -- error #7: the syntax for a loop uses IN not AS
    -- error #8: you need to declare R before you can use it
    -- error #9: the SELECT for a cursor loop must NOT be terminated with a ;
    FOR r IN SELECT nofvotes FROM votes WHERE cid=scid AND bno=sbno
    loop  -- error #10: you need to use LOOP, not DO

        sum_ := sum_ + r.nofvotes;  -- error #11: you need to use := for an assignment, not SET
    end loop; -- error #12: it's END LOOP
              -- error #13: you need to terminate the statement with a ;

    if sum_ > max_ then 
       return 0;
    else
       return 1;
    end if; -- error #14: an END if is required
END;
$body$
language plpgsql; -- error #14: you need to specify the language

手册记录了这一切:

• 错误 #1，#2:http://www.postgresql.org/docs/current/static/sql-createfunction.html
• 错误#3:http://www.postgresql.org/docs/current/static/plpgsql-structure.html
• 错误#6:http://www.postgresql.org/docs/current/static/plpgsql-declarations.html
• 错误 #7、#8、#9、#10、#12:http://www.postgresql.org/docs/current/static/plpgsql-control-structures.html#PLPGSQL-RECORDS-ITERATING
• 错误#11:http://www.postgresql.org/docs/current/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-ASSIGNMENT
• 错误#14:http://www.postgresql.org/docs/current/static/plpgsql-control-structures.html#PLPGSQL-CONDITIONALS

---

不需要整个 FOR 循环，而且效率极低。它可以替换为:

SELECT sum(nofvotes)
  into sum_
FROM votes 
WHERE cid=scid AND bno=sbno;

Postgres 有一个原生的 bool 类型，最好使用它而不是整数。如果将函数声明为 returns boolean，则最后一行可以简化为

return max_ > sum_;

---

这部分:

 select totvoters
   into max_
 from ballotbox 
 WHERE cid=scid AND bno=sbno;

将仅在 cid,bno 在表格投票箱中是唯一的情况下工作。否则，如果 select 返回多行，您可能会在运行时出错。

---

假设 ballotbox 上的选择确实使用主(或唯一)键，整个函数可以简化为一个小的 SQL 表达式:

create function trigf1(sbno integer, scid numeric(4,0))
  returns boolean
as
$body$
  return (select totvoters from ballotbox WHERE cid=scid AND bno=sbno) > 
         (SELECT sum(nofvotes) FROM votes WHERE cid=scid AND bno=sbno);
$body$
language sql;