如何从一个函数postgres返回多个值

Question

我有这两个函数正在返回光标并动态创建文本，但是我需要在一个函数中执行。

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和函数必须返回两者。

谢谢。

Answer 1

要将值返回到返回表的函数，我们必须在函数内包含返回查询语句。

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';