Syntax error when create Postgres function

Question

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'

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

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)

Answer 1

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

---

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

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

return max_ > sum_;

---

This part:

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

will only work 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.

---

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

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;

Answer 2

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

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;

Answer 3

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

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
$$