我有下面的plpgsql函数,它在第8.3页及更高版本上运行得很好,但我需要将其翻译回第8.1页的数据库,我无法将其拼接。
有什么建议吗?我需要去掉"返回查询",因为它还没有在8.1中引入…
CREATE OR REPLACE FUNCTION specie_children (specie_id INT, self BOOLEAN)
RETURNS SETOF specie AS
$BODY$
DECLARE
r specie%ROWTYPE;
BEGIN
IF self THEN
RETURN QUERY SELECT * FROM specie WHERE specieid = specie_id;
END IF;
FOR r IN SELECT * FROM specie WHERE parent = specie_id
LOOP
RETURN NEXT r;
RETURN QUERY SELECT * FROM specie_children(r.specieid, FALSE);
END LOOP;
RETURN;
END
$BODY$
LANGUAGE 'plpgsql';
我该如何翻译?
RETURN QUERY SELECT * FROM specie_children(r.specieid, FALSE);
可以重写为
for r2 in select * from specie_children(r.specieid, FALSE)
loop
return next r2
end loop
快速演示。基本上@maniek已经给出了答案。
测试表:
CREATE TEMP TABLE specie(specieid int, parent int);
INSERT INTO specie VALUES
(1,0), (2,0), (3,0)
,(11,1), (12,1), (13,1)
,(111,11), (112,11), (113,11);
重写功能:
CREATE OR REPLACE FUNCTION specie_children (specie_id INT, self BOOLEAN)
RETURNS SETOF specie AS
$BODY$
DECLARE
r specie%ROWTYPE;
BEGIN
IF self THEN
FOR r IN SELECT * FROM specie WHERE specieid = $1
LOOP
RETURN NEXT r;
END LOOP;
END IF;
FOR r IN SELECT * FROM specie WHERE parent = $1
LOOP
RETURN NEXT r;
FOR r IN SELECT * FROM specie_children(r.specieid, FALSE)
LOOP
RETURN NEXT r;
END LOOP;
END LOOP;
RETURN;
END;
$BODY$
LANGUAGE plpgsql;
呼叫:
SELECT * FROM specie_children (1, true);
退货:
specieid | parent
---------+-------
1 | 0
11 | 1
111 | 11
112 | 11
113 | 11
12 | 1
13 | 1