How to return multiple values from one function postgres-CodePudding

I have these two functions that are returning cursor and dynamically created text, but I need to do it inside one function.

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

I've tried to find how to do it and haven't come across any solution. I thought about making it inside a table, but I don't know if that's possible and didn't find anything about this.

I am not experienced with SQL at all, so don't know if such a thing is possible.

It's a task and the rules are that there needs to be a cursor, dynamic sql and function must return them both.

Thanks.

CodePudding user response：

To return value to a function that returns table, we have to include RETURN QUERY statement inside function.

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