How to return multiple values from one function postgres

Question

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.

Welcome to SO. Perhaps changing the function to return a table (with two columns) instead of a single column would do ;) — Jim Jones
– Jim Jones, Commented Jun 28, 2022 at 9:31
Welcome, do you maybe know how could I do it when I create this cursor inside a function? I found this sqlservertutorial.net/sql-server-user-defined-functions/… but completely don't know how to select my cursor to show it. — Szarlej
– Szarlej, Commented Jun 28, 2022 at 9:36
What result are you trying to get? How would you use both results? Using cursors is not particularly common in Postgres. It's not clear to me why you might need one here. — Jeremy
– Jeremy, Commented Jun 28, 2022 at 10:05
It's just a task and these are the rules, I'm on apprenticeships and get tasks from a supervisor to do and now I'm stuck. — Szarlej
– Szarlej, Commented Jun 28, 2022 at 10:11

swapnil solanki · Accepted Answer · 2022-06-29 08:48:24Z

1

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

answered Jun 29, 2022 at 8:48

swapnil solanki

865 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to return multiple values from one function postgres

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related