CODEWITHSUNDEEP
postgresql
Anastasios Papanikolaou
Anastasios Papanikolaou

Reputation: 77

Return results from cursor as table

I work with Postgres9.5. I have an example which successfully returns the results of a cursor as a string (concatenation of rows): Here is the example:

CREATE OR REPLACE FUNCTION get_doctor_appoint()
RETURNS TEXT AS $$
DECLARE 
Names TEXT DEfault '';
rec_appoint RECORD;
doctor_appoint cursor 
FOR SELECT * FROM appointments 
where doctorAMKA = (SELECT doctoramka FROM doctor WHERE username='[email protected]')
AND t>'2017-4-6 00:00:00' AND t<'2017-5-6 00:00:00';
BEGIN
OPEN doctor_appoint; 

LOOP
FETCH doctor_appoint INTO rec_appoint;
EXIT WHEN NOT FOUND;
Names:=Names||','||rec_appoint.t||':'||rec_appoint.patientamka;
END LOOP;
CLOSE doctor_appoint;
RETURN Names;
END; $$
LANGUAGE plpgsql;

I would like to return the results as a table but haven't found an example that does that.

Upvotes: 0

Views: 96

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246228

You declare the function as RETURNS SETOF text.

For every row you want to return, use RETURN NEXT text_value;.

To end function execution, use RETURN or drop out at the bottom end of the function.

Upvotes: 1

Related Questions