如何使用plpgsql在postgres函数中递归返回表?不使用CTE/With Recursive

Question

我试着在这里实现它，如下

create or replace function getTextEditRecord(textId integer)
RETURNS Table (
text_id integer,
text_details character varying,
new_text_id integer) AS $$
DECLARE
curr_rec record;
temp_rec record;
BEGIN
curr_rec :=
(select tm.text_id, tm.text_details, tm.new_text_id from text_master tm
where tm.text_id = textId);
IF FOUND THEN
IF curr_rec.text_id != curr_rec.new_text_id THEN
temp_rec := getTextEditRecord(curr_rec.new_text_id);
--RETURN TABLE HERE
ELSE
-- No Recursive call directly return table
--RETURN TABLE HERE
END IF;
END IF;
--RETURN TABLE HERE
END;
$$ Language plpgsql;

现在我试着在谷歌上搜索，但可以找到如何将记录类型转换为表类型。如在将temp_rec转换为表类型并返回中一样。

Answer 1

通过递归函数执行此操作是最低效的方法

但无论如何，你可以做这样的事情：

create or replace function get_text_edit_record(p_text_id integer)
RETURNS Table (
text_id integer,
text_details varchar,
new_text_id integer) 
AS $$
DECLARE
curr_rec record;
BEGIN
select tm.text_id, tm.text_details, tm.new_text_id 
into curr_rec
from text_master tm
where tm.text_id = p_text_id;
IF FOUND THEN
return query 
select curr_rec.text_id, curr_rec.text_details, curr_rec.new_text_id;
--- IS DISTINCT FROM properly deals with NULL values
IF curr_rec.text_id IS DISTINCT FROM curr_rec.new_text_id THEN
return query 
select * 
from get_text_edit_record(curr_rec.new_text_id);
END IF;
END IF;
END;
$$ Language plpgsql;