How to create a procedure that returns a set of rows from a table in postgreSQL

Question

How do i create a Procedure that returns a set of rows from a table? or is it even possible to return a tabular result set with procedure. I tried adding returns setof students like you do in a function and table(id int) but it doesn't work.

SAMPLE CODE:

CREATE OR REPLACE PROCEDURE getStudents()
LANGUAGE plpgsql
AS $$
BEGIN
 SELECT * FROM STUDENTS
COMMIT;
RETURN;
END;
$$;

I can call it but it says query has no destination for result data

http://www.postgresqltutorial.com/plpgsql-function-returns-a-table/ ... Literraly 3s of google searching... — Jaisus, Dec 26 '19 at 09:30
procedures aren't meant to return something. You have to use a function — , Dec 26 '19 at 09:33
I f you want to return a value from a piece of code : do it with a function. A procedure is not suitable for this. (https://stackoverflow.com/questions/721090/what-is-the-difference-between-a-function-and-a-procedure/721107) — Jaisus, Dec 26 '19 at 09:34
less hate people... I am on the same page as the guy that made the question, probably he is coming from SQL and we are used to return value on PROCEDURES not FUNCTION.... If we do not make the right question for google, it will not take 3s to get an answer... — MalachiteBR, Aug 11 '21 at 23:39

score 3 · Accepted Answer · answered Dec 26 '19 at 09:40

Procedures aren't meant to return data, that's what functions are for.

You can use a plain SQL function for this, no need for PL/pgSQL:

CREATE OR REPLACE funct get_students()
   returns setof student
   LANGUAGE sqö
AS $$
  select *
  from students;
$$;

Then use it like a table:

select *
from get_students();

There is also no need for a commit.

score 1 · Answer 2 · answered Dec 26 '19 at 09:36

Try to use function instead of procedure. I usually use this.

You need to create a ctype for fetching the data. Put whatever columns you have to fetch from STUDENTS table. Syntax is as follows:

CREATE TYPE students_data_ctype AS
(
column_1 int4,
column_2 varchar(100),
column_3 varchar(500)
)

Then create a funcction :

CREATE
    OR
REPLACE
    FUNCTION PUBLIC.getStudents 
    ()
    RETURNS SETOF students_data_ctype AS $BODY$ DECLARE res 
    students_data_ctype; 
BEGIN
    FOR res IN 
SELECT
    column_1,
    column_2,
    column_3
FROM
    STUDENTS 
LOOP RETURN NEXT res; 
    END LOOP; 
END
    ; $BODY$ LANGUAGE 'plpgsql'
GO

Function call :

Select * FROM getStudents()

Taddaaa! You will get your data.

There is no need to create a type to create a set returning function, you can declare the function as `returns table (...)`. The language name is an identifier, it should not be quoted. — , Dec 26 '19 at 09:42

How to create a procedure that returns a set of rows from a table in postgreSQL

2 Answers2