PostgreSQL function definition "syntax error at or near +" Suppose a simple query:

(SELECT MAX(timestamp) FROM events e WHERE e.id &lt; some_id) 
  + ((SELECT MIN(timestamp) FROM events e WHERE e.id &gt; some_id) 
    - (SELECT MAX(timestamp) FROM events e WHERE e.id &lt; some_id)) / 2


If some_id is the parameter, this finds the approximate timestamp based on some sequence of events (it takes the previous and following event and averages their timestamps).

This query works great, now I want to package it as a function:

CREATE FUNCTION id_to_timestamp(integer) RETURNS timestamp with time zone AS
$BODY$
    (SELECT MAX(timestamp) FROM events e WHERE e.id &lt; $1) 
      + ((SELECT MIN(timestamp) FROM events e WHERE e.id &gt; $1) 
        - (SELECT MAX(timestamp) FROM events e WHERE e.id &lt; $1)) / 2
$BODY$
LANGUAGE sql;


This suddenly fails with message 

ERROR:  syntax error at or near "+"
... events e WHERE e.id &lt; $1) + ((SELECT...


What am I doing wrong? Why is the + operator problematic?

(The actual identifiers are anonymized.)
A LANGUAGE sql function must be a complete SQL statement (or more than one).

Yours is just an expression.

Just prepend SELECT, like you would when running it stand-alone.

$BODY$
SELECT (SELECT ...


The reason it complains at the + is that, surprisingly, it's legal to parenthesise a top-level query. This, for example, is a valid query:

regress=&gt; (SELECT 1);
 ?column? 
----------
        1
(1 row)


(I had no idea about that until your question, either, so thanks. I'm not even sure it's intentional...)

Note that you'd have got the same error if you ran your original query as-written, so I assume you'd prepended SELECT to it.

regress=&gt; (SELECT 1) + (SELECT 2);
ERROR:  syntax error at or near "+"
LINE 1: (SELECT 1) + (SELECT 2);