postgresql plpgsql function returns syntax error at end of input

Question

I was writing a PlPgSQL function in

greenplum

and the function needs to transfer the

interval

which

< interval '00:00:00'

to a positive value.

For example, the interval value

-23:57:00

shoule be transfered to

00:03:00

. So I wrote the function like this below:

CREATE OR REPLACE FUNCTION abstime(timeval INTERVAL)

RETURNS INTERVAL AS $$

BEGIN

IF timeval < INTERVAL '00:00:00' THEN

RETURN timeval + INTERVAL '24:00:00';

ELSE

RETURN timeval;

END IF;

RETURN;

END;

$$ LANGUAGE plpgsql;

after I entered, I got these:

ERROR:  syntax error at end of input

LINE 1: SELECT

           ^

QUERY:  SELECT

CONTEXT:  SQL statement in PL/PgSQL function "abstime" near line 7

I am not sure what's going wrong, and plz help.

Answer 1

Modern PostgreSQL has little bit better diagnostic:

postgres=# CREATE OR REPLACE FUNCTION abstime(timeval INTERVAL)
RETURNS INTERVAL AS $$
BEGIN
  IF timeval < INTERVAL '00:00:00' THEN
    RETURN timeval + INTERVAL '24:00:00';
  ELSE
    RETURN timeval;
  END IF;
  RETURN; --- SHOULD BE REMOVED
END;                
$$ LANGUAGE plpgsql;
ERROR:  missing expression at or near ";"
LINE 9: RETURN;
              ^

The return on the line 9 is useless and missing a expression. You have to remove line 9. In this case the expression is required in RETURN statement.

postgresql plpgsql function returns syntax error at end of input

Sign up