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

Syntax error when create Postgres function

提问by Tom Klino

I'm trying to create a function that sums the result of all of one query's values and compares it to a number of another, simple query.

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

This is what I have, however I'm getting a syntax error near begin (2nd line):

这就是我所拥有的，但是我在开始（第二行）附近遇到语法错误：

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

This results in:

这导致：

Syntax error near 'BEGIN'

'BEGIN' 附近的语法错误

I'm using postgreSQL and the pgadminIII (just in case it is relevant).

我正在使用 postgreSQL 和 pgadminIII（以防万一）。

I have no idea why I'm getting this error, everything seems to be exactly as the textbook defined. (This is the text book I'm using: http://digilib.usu.ac.id/buku/107859/Database-systems-concepts,-6th-ed.html)

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

回答by a_horse_with_no_name

I don't know which "textbook" you were using but if everything you wrote is exactly as in that book, that book is totally wrong:

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

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

The manual documents all this:

手册记录了所有这些：

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

• 错误 #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

---

---

The whole FORloop is not needed and extremely inefficient. It can be replaced with:

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

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

Postgres has a native boolean type, it's better to use that instead of integers. If you declare the function as returns boolean, the last line can be simplified to

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

return max_ > sum_;

---

---

This part:

这部分：

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

will onlywork if cid,bno is unique in the table ballotbox. Otherwise you might get an error at runtime if the select returns more than one row.

将唯一的工作，如果CID，BNO在表ballotbox独特。否则，如果选择返回多于一行，您可能会在运行时收到错误消息。

---

---

Assuming that the select on ballotboxdoes use the primary (or a unique) key, the whole function can be simplified to a small SQL expression:

假设 select onballotbox确实使用了主（或唯一）键，整个函数可以简化为一个小的 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;

回答by Gustav CFC

I am not really a postgresSQL person, but I would have thought

我不是真正的 postgresSQL 人，但我会想

declare max as SELECT totvoters FROM ballotbox WHERE cid=scid AND bno=sbno;

should be

应该

declare max := SELECT totvoters FROM ballotbox WHERE cid=scid AND bno=sbno;

回答by Guffa

The body of the function should be a string after the askeyword, i.e. as 'code...'. Usually a dollar-quoted stringis used:

函数体应该是as关键字后的字符串，即as 'code...'. 通常使用美元引用的字符串：

CREATE FUNCTION trigf1(sbno integer, scid numeric(4,0)) RETURNS integer
AS $$
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
$$