Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Suppose a simple query:

(SELECT MAX(timestamp) FROM events e WHERE e.id < some_id) 
  + ((SELECT MIN(timestamp) FROM events e WHERE e.id > some_id) 
    - (SELECT MAX(timestamp) FROM events e WHERE e.id < 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 < $1) 
      + ((SELECT MIN(timestamp) FROM events e WHERE e.id > $1) 
        - (SELECT MAX(timestamp) FROM events e WHERE e.id < $1)) / 2
$BODY$
LANGUAGE sql;

This suddenly fails with message

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

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

(The actual identifiers are anonymized.)

share|improve this question

put on hold as off-topic by dezso, Paul White, RolandoMySQLDBA, Phil, Max Vernon 23 hours ago

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – dezso, Paul White, RolandoMySQLDBA, Phil, Max Vernon
If this question can be reworded to fit the rules in the help center, please edit the question.

    
Thanks for actually showing a complete error message and the relevant queries. Wish more people did without being asked. –  Craig Ringer yesterday
    
Welcome :) The table names etc are anonymized of course, but this is the least I could do. –  vektor yesterday
1  
It's nice if you mention that, since sometimes even the most careful people make mistakes when anonymizing their identifiers. I've had very confusing and frustrating issues that turned out to be caused by someone "anonymizing" their schema/queries, not mentioning that, and getting it wrong... –  Craig Ringer yesterday

1 Answer 1

up vote 7 down vote accepted

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=> (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=> (SELECT 1) + (SELECT 2);
ERROR:  syntax error at or near "+"
LINE 1: (SELECT 1) + (SELECT 2);
share|improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.