如果初始SELECT在单个语句中一无所获，则返回第一个可用行

Question

我正在尝试组合一个SQL查询，该查询将基于简单的WHERE条件返回数据，但如果不满足该条件，也将返回第一个可用记录。

author_id	post_id	publish_date
1	31	2021-07-23
2	432	2021-06-22
2	555	2020-07-23

只需一个查询即可：

WITH cte AS (
SELECT *
FROM   posts
WHERE  author_id = 1
AND    publish_date > '2021-07-01'
ORDER  BY publish_date
)
TABLE cte
UNION ALL
(  -- parentheses required
SELECT *
FROM   posts
WHERE  NOT EXISTS (SELECT FROM cte)
AND    author_id = 1
AND    publish_date <= '2021-07-01'
ORDER  BY publish_date DESC NULLS LAST
LIMIT  1
);

相关：

• 组合3个SELECT语句输出1个表

或者使用PL/pgSQL函数：

CREATE OR REPLACE FUNCTION my_func(_author_id int, _publish_date date)
RETURNS SETOF posts
LANGUAGE plpgsql aS
$$
BEGIN
RETURN QUERY
SELECT *
FROM   posts
WHERE  author_id = _author_id
AND    publish_date > _publish_date
ORDER  BY publish_date;

IF NOT FOUND THEN
RETURN QUERY
SELECT *
FROM   posts
WHERE  author_id = _author_id
AND    publish_date <= _publish_date
ORDER  BY publish_date DESC NULLS LAST
LIMIT  1;
END IF;
END
$func$;

呼叫：

SELECT * FROM my_func(2,'2021-07-01');

相关：

• PLpgSQL函数未返回匹配的标题
• 如何在PostgreSQL中返回函数内部的SELECT结果

您可以为此创建一个函数，并获得您想要的实现条件：

create or replace function some_func(input_author_id integer, input_date date)
returns setof posts
as
$$
declare
selected_posts posts%rowtype;
begin
select * from posts p into selected_posts where p.author_id = input_author_id and p.publish_date > input_date;
if not found then
return query
select * from posts p where p.author_id = input_author_id order by publish_date desc limit 1;
else 
return query
select * from posts p where p.author_id = input_author_id and p.publish_date > input_date;
end if;
end
$$
language plpgsql;

之后，只需传递所需参数的值：

select * from some_func(2,'2021-07-01');

这将给你以下结果：

author_id	post_id	publish_date
2	432	2021-06-22