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?

Solution

Pavel is 100% correct on how to fix the function but based on the function code and because you are using Greenplum, I am guessing you are wanting to select this inline against a large table. When doing this, you'll want to avoid the overhead of PL/pgSQL. It is a great language and I use it often but I would avoid it for inline SQL. Use PL/pgSQL for encapsulating transformation logic, not inline SQL.

Secondly, I would make the function IMMUTABLE because you aren't updating the database in the function and the same value always returns for a given parameter. More on that here: http://www.postgresql.org/docs/8.2/static/xfunc-volatility.html

Proof:

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;
END;
$$ LANGUAGE plpgsql;

Now get the explain plan.

EXPLAIN ANALYZE SELECT abstime('04:00:00'::interval);

Result  (cost=0.01..0.02 rows=1 width=0)
  Rows out:  1 rows with 5.230 ms to end, start offset by 0.124 ms.
  InitPlan
    ->  Result  (cost=0.00..0.01 rows=1 width=0)
          Rows out:  1 rows with 5.146 ms to end of 2 scans, start offset by 0.130 ms.
Slice statistics:
  (slice0)    Executor memory: 63K bytes.
  (slice1)    Executor memory: 37K bytes.
Statement statistics:
  Memory used: 128000K bytes
Settings:  optimizer=on
Optimizer status: legacy query optimizer
Total runtime: 5.356 ms

Now, rewriting this function as a SQL function and with the IMMUTABLE flag.

CREATE OR REPLACE FUNCTION abstime_v2(timeval INTERVAL)
RETURNS INTERVAL AS 
$$
SELECT CASE WHEN $1 < INTERVAL '00:00:00' THEN $1 + INTERVAL '24:00:00' ELSE $1 END;
$$ 
LANGUAGE sql IMMUTABLE;

The explain plan.

EXPLAIN ANALYZE SELECT abstime_v2('04:00:00'::interval);

Result  (cost=0.00..0.00 rows=1 width=16)
  Rows out:  1 rows with 0.002 ms to end, start offset by 0.042 ms.
  ->  Result  (cost=0.00..0.00 rows=1 width=1)
        Rows out:  1 rows with 0.001 ms to end, start offset by 0.043 ms.
Slice statistics:
  (slice0)    Executor memory: 13K bytes.
Statement statistics:
  Memory used: 128000K bytes
Settings:  optimizer=on
Optimizer status: PQO version 1.607
Total runtime: 0.046 ms

There are less scans and memory needed for the v2 function.