CODEWITHSUNDEEP
sqlpostgresql
Tom Klino
Tom Klino

Reputation: 2524

Syntax error when create Postgres function

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)

Upvotes: 3

Views: 15509

Answers (3)

user330315
user330315

Reputation:

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:

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;

Upvotes: 16

Guffa
Guffa

Reputation: 700840

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

Upvotes: 0

Gustav CFC
Gustav CFC

Reputation: 1

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;

Upvotes: 0

Related Questions