我有這兩個函式回傳游標和動態創建的文本，但我需要在一個函式中完成。
CREATE OR REPLACE FUNCTION get_students2(classId integer)
RETURNS refcursor
LANGUAGE plpgsql
AS
$$
DECLARE
students text DEFAULT '';
student record;
cursor_students CURSOR(classId integer)
FOR SELECT firstName, surname
FROM tests.students
WHERE class = classId;
BEGIN
OPEN cursor_students(classId);
LOOP
FETCH cursor_students INTO student;
EXIT WHEN NOT FOUND;
students := students || ' ' || student.firstName || ' ' || student.surname;
END LOOP;
CLOSE cursor_students;
RETURN cursor_students;
END;
$$
CREATE OR REPLACE FUNCTION get_students(classId integer)
RETURNS refcursor
LANGUAGE plpgsql
AS
$$
DECLARE
students text DEFAULT '';
student record;
cursor_students CURSOR(classId integer)
FOR SELECT firstName, surname
FROM tests.students
WHERE class = classId;
BEGIN
OPEN cursor_students(classId);
LOOP
FETCH cursor_students INTO student;
EXIT WHEN NOT FOUND;
students := students || ' ' || student.firstName || ' ' || student.surname;
END LOOP;
CLOSE cursor_students;
RETURN students;
END;
$$
我試圖找到如何做到這一點，并沒有遇到任何解決方案。我想把它放在一張桌子里，但我不知道這是否可能，也沒有找到任何關于這個的東西。
我根本沒有使用 SQL 的經驗，所以不知道這樣的事情是否可能。
這是一項任務，規則是需要有一個游標、動態 sql 和函式必須同時回傳它們。
謝謝。
uj5u.com熱心網友回復：
要將值回傳給回傳表的函式，我們必須在函式中包含 RETURN QUERY 陳述句。
CREATE OR REPLACE FUNCTION get_students(classId int)
RETURNS TABLE (
students_cursor refcursor,
students_list text
)
AS $$
DECLARE
students text DEFAULT '';
student record;
cursor_students CURSOR(classId integer)
FOR SELECT firstName, surname
FROM students
WHERE class = classId;
BEGIN
OPEN cursor_students(classId);
LOOP
FETCH cursor_students INTO student;
EXIT WHEN NOT FOUND;
students := students ' ' student.firstName ' ' student.surname;
END LOOP;
CLOSE cursor_students;
RETURN QUERY
SELECT cursor_students,students;
END; $$
LANGUAGE 'plpgsql';
轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/497552.html
標籤：sql PostgreSQL plpgsql