sql - 使用函数处理负偏移量

Question

我正在创建一个 Postgresql 函数来处理负偏移量。当偏移量为负时，我的函数应返回 0。但是，当我尝试运行此函数时，“行”附近出现错误，我不确定为什么。我的条件分支有什么问题？

CREATE OR REPLACE FUNCTION 
    calc_offset(row integer, padding integer) RETURNS integer AS $$
    BEGIN
        if (row-1-padding) < 0 then return 0;
        else return (row-1-padding);
    END;
$$ LANGUAGE plpgsql;

WITH all_the_trimmings AS (
    SELECT ui.id, ui.name, ui.time, 
           row_number() over(order by name asc) as rownumber
    FROM user_infos ui

  ), my_row AS (
    SELECT * FROM all_the_trimmings WHERE id=1
  ), the_slice AS (
    SELECT * FROM all_the_trimmings LIMIT 5 
       OFFSET calc_offset((SELECT rownumber FROM my_row)::int,2)
  )
SELECT * from my_row
UNION ALL
SELECT * from the_slice;

SQL fiddle for the full picture

Answer 1

您的函数 calc_offset() 可以用这个简单的 SQL 函数替换:

CREATE OR REPLACE FUNCTION calc_offset(p_row int, padding int)
  RETURNS int AS 'SELECT GREATEST ($1 - $2 - 1, 0)' LANGUAGE sql;

而且您根本不需要该函数。只需使用 GREATEST使用子选择:

WITH all_the_trimmings AS (
   SELECT ui.id, ui.name, ui.time, row_number()  OVER (ORDER BY name) AS rn
   FROM   user_infos ui
   )
, my_row AS (
   SELECT * FROM all_the_trimmings WHERE id=1
   )
SELECT * FROM my_row
UNION ALL
SELECT * FROM all_the_trimmings LIMIT 5 
OFFSET GREATEST ((SELECT rn - 3 FROM my_row), 0);

或者更简单:

WITH cte AS (
   SELECT ui.id, ui.name, ui.time, row_number() OVER (ORDER BY name) AS rn
   FROM   user_infos ui
   )
SELECT * FROM cte WHERE id = 1
UNION ALL
SELECT * FROM cte LIMIT 5 
OFFSET GREATEST ((SELECT rn - 3 FROM cte WHERE id = 1), 0);

SQL Fiddle showing both.