Abner Soong Abner Soong - 6 months ago 24
SQL Question

Greenplum plpgsql function returns syntax error at end of input

I was writing a PL/pgSQL function in Greenplum and the function needs to translate the

interval
which
< interval '00:00:00'
to a positive value.

For example, the interval value
-23:57:00
should be translated to
00:03:00
. So I wrote this function:

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:


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?

Answer

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.

Comments