sql - 带有 CASE 的 Postgres 自定义函数

Question

我有表 watchers:

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

我尝试根据 time_type 和 time_count 值获取整数:

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

但是我得到一个错误:

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 **********

用法:

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

如果 time_type = 'hour' 和 time_count = 1 输出应该是 3600 秒。

我的例子有什么问题:

我用了https://www.postgresql.org/docs/7.4/static/functions-conditional.html

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

Answer 1

一个函数只能返回一个值，所以你可能打算这样做:

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;

但正如@Mureinik 指出的那样，您甚至不需要执行SELECT；直接使用 CASE 表达式即可。