问题:创建 Postgres 函数时语法错误

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

这就是我所拥有的,但是在开始(第 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

这导致:

'BEGIN' 附近的语法错误

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

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

解答

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

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 有一个原生的布尔类型,最好使用它而不是整数。如果将函数声明为returns boolean,最后一行可以简化为

return max_ > sum_;

这部分:

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

如果 cid,bno 在表格投票箱中是唯一的,则仅有效。否则,如果选择返回多行,您可能会在运行时收到错误。

假设ballotbox上的 select 确实使用了主键(或唯一键),那么整个函数可以简化为一个小 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;