Postgres custom function with CASE

Question

I have table watchers:

------------------------------------------
time_type | time_count | timestamp_created
------------------------------------------
  'hour'  |   1        |   2016-12-08 15:56:26.169614
  'hour'  |   13       |   ... 
  'day'   |   5        |   ...

I try to get integer based on time_type and time_count values:

CREATE OR REPLACE FUNCTION af_calculate_range(tt text,tc  integer) RETURNS integer AS $$ 
            SELECT tt, CASE tt WHEN 'day' THEN tc * 60 * 60 
                               WHEN 'hour' THEN tc * 60
                       END
        FROM watchers;

          $$
    LANGUAGE SQL;

SELECT af_calculate_range(time_type, time_count) FROM watchers

but I get an error:

ERROR:  return type mismatch in function declared to return integer  
DETAIL:  Final statement must return exactly one column. 
CONTEXT:  SQL function "af_calculate_range"
********** Error **********

Usage:

SELECT * FROM watchers
WHERE  EXTRACT(EPOCH FROM now()) > (EXTRACT(EPOCH FROM timestamp_created) +
 af_calculate_range(time_type, time_count) )

if time_type = 'hour' and time_count = 1 the output should be 3600 seconds.

What's wrong with my example:

I used https://www.postgresql.org/docs/7.4/static/functions-conditional.html

and https://www.postgresql.org/docs/9.1/static/sql-createfunction.html

Answer 1

A function can only return a single value, so you probably intended to do this:

CREATE OR REPLACE FUNCTION af_calculate_range(tt text, tc integer) RETURNS integer AS $$ 
        SELECT CASE WHEN tt = 'day' THEN tc * 60 * 60    -- return a (single) scalar 
                    WHEN tt = 'hour' THEN tc * 60
               END
        FROM watchers;
      $$
LANGUAGE SQL;

But as @Mureinik pointed out, you don't even need to do a SELECT; just use the CASE expression directly.