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.)