SQL (Postgres) function definition - RETURNS

Question

I am trying to create a very simple PostgreSQL function, though I keep getting a very strange syntax error. The syntax I use is different from anything I have seen online (though this is the one the textbook uses), and thus I can't figure out why it fails...

This is the SQL:

CREATE OR REPLACE FUNCTION gsum(graphID integer)
    RETURNS integer
    BEGIN
        DECLARE total integer DEFAULT 0
        SELECT sum(weight) INTO total
        FROM Edge
        WHERE gno = graphID
    RETURN total;
    END;

The error is:

ERROR:  syntax error at or near "BEGIN"
LINE 3:  BEGIN
         ^
********** Error **********

ERROR: syntax error at or near "BEGIN"
SQL state: 42601
Character: 68

Your syntax looks like PSM - but it is wrong in PostgreSQL - There more languages than one are supported (and it needs little bit strange syntax for CREATE FUNCTION). PLpgSQL is based on ADA language - or more on PL/SQL - what is simplified ADA. — Pavel Stehule
– Pavel Stehule, Commented Jul 17, 2014 at 5:11

Community · Accepted Answer · 2017-05-23 11:57:51Z

2

Your basic mistakes:

DECLARE must come before BEGIN.
Statements need to be terminated with ;.
Function body of a plpgsql function is a string and needs to be quoted. Use dollar-quoting to avoid complications with quotes in the body.
Missing keyword AS.
Missing language declaration LANGUAGE plpgsql.
Type mismatch.
You don't need a default.
This would still return NULL if the sum is NULL.

CREATE OR REPLACE FUNCTION gsum(graphID integer)
  RETURNS integer AS
$func$
DECLARE
   total integer;
BEGIN
   SELECT sum(weight)::int INTO total
   FROM   edge
   WHERE  gno = graphID;

   RETURN COALESCE(total, 0);
END
$func$ LANGUAGE plpgsql;

And you'd better use a simple SQL function for this like @Clodoaldo advised. Just add COALESCE().

edited May 23, 2017 at 11:57

CommunityBot

11 silver badge

answered Jul 16, 2014 at 13:43

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Clodoaldo Neto · Accepted Answer · 2014-07-17 08:33:12Z

1

It can be plain SQL in instead of plpgsql

create or replace function gsum(graphid integer)
returns bigint as $$

    select sum(weight) as total
    from edge
    where gno = graphid;

$$ language sql;

Notice that if weight is integer sum will return bigint not integer.

edited Jul 17, 2014 at 8:33

answered Jul 16, 2014 at 13:33

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

2 Comments

byteSlayer Over a year ago

Is it possible to accomplish the task with the type of syntax I use?

Clodoaldo Neto Over a year ago

@byteSlayer Check Erwin's answer

Ilesh Patel · Accepted Answer · 2014-07-16 13:29:04Z

0

CREATE OR REPLACE FUNCTION  gsum(graphID integer)
    RETURNS integer AS
    $BODY$        
DECLARE 
    total integer DEFAULT 0;
    BEGIN

        SELECT sum(weight) INTO total
        FROM Edge
        WHERE gno = graphID;
    RETURN total;
    END;
    $BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

answered Jul 16, 2014 at 13:29

Ilesh Patel

2,15516 silver badges27 bronze badges

Collectives™ on Stack Overflow

SQL (Postgres) function definition - RETURNS

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related