How to get result set from PostgreSQL stored procedure?

Question

I created a stored procedure in PostgreSQL 11 to perform CRUD operation, and it works fine for 1. Create 2. Update 3. Delete, but while I run read command by passing Condition = 4 to select a result set, I get below error.

I have used PostgreSQL function to get result set it works for me, but I need to get result using the PostgreSQL stored procedure.

Here is my code for stored procedure:

CREATE OR REPLACE PROCEDURE public.testSpCrud(
    fnam text,
    lnam text,
    id integer,
    condition integer)
LANGUAGE 'plpgsql'

AS $BODY$
declare
    countOfDisc int; 
BEGIN
if condition=1 then

INSERT INTO public.employee(
    employeeid, lname, fname, securitylevel, employeepassword, hphonearea, hphone, cphonearea, cphone, street, city, state, zipcode, extzip, name, email, groomerid, type, commission, inactive, carrierid, notoallemployees, languageid, isdogwalker, ispetsitter, ismobilegroomer, ssma_timestamp)
    VALUES (4,  'Test', 'Test', 2, 2, 32, 32, 32, 32, 32, 32,32, 32, 32, 22, 22, 2, 2, 2, false, 223,true, 223, true, true, true, '2019-08-27');
end if;
 if condition =2 then
    delete from Employee where employeeid=id;
    end if;
     if condition =3 then
    update Employee set fname='Test' where employeeid=id;
    end if;
     if condition =4 then
         Select * from Employee;
    end if;
    END;
$BODY$;

ERROR: query has no destination for result data
HINT: If you want to discard the results of a SELECT, use PERFORM instead.
CONTEXT: PL/pgSQL function testspcrud(text,text,integer,integer) line 22 at SQL statement
SQL state: 42601

Answer 1

As of Postgres 13, returning from a PROCEDURE is still very limited. See:

• How to return a value from a stored procedure (not function)?

Most likely, you fell for the widespread misnomer "stored procedure" and really want a FUNCTION instead, which can return a value, a row or a set according to its declaration.

• In PostgreSQL, what is the difference between a “Stored Procedure” and other types of functions?
• How to return result of a SELECT inside a function in PostgreSQL?

Would work like this:

CREATE OR REPLACE FUNCTION public.testSpCrud(
    fnam text,
    lnam text,
    id integer,
    condition integer)
  RETURNS SETOF Employee LANGUAGE plpgsql AS
$func$
BEGIN
   CASE condition
   WHEN 1 THEN
      INSERT INTO public.employee(
       employeeid, lname, fname, securitylevel, employeepassword, hphonearea, hphone, cphonearea, cphone, street, city, state, zipcode, extzip, name, email, groomerid, type, commission, inactive, carrierid, notoallemployees, languageid, isdogwalker, ispetsitter, ismobilegroomer, ssma_timestamp)
       VALUES (4,  'Test', 'Test', 2, 2, 32, 32, 32, 32, 32, 32,32, 32, 32, 22, 22, 2, 2, 2, false, 223,true, 223, true, true, true, '2019-08-27');

   WHEN 2 THEN
      DELETE FROM Employee WHERE employeeid=id;

   WHEN 3 THEN
      UPDATE Employee SET fname='Test' WHERE employeeid=id;

   WHEN 4 THEN
      RETURN QUERY
      SELECT * FROM Employee;

   ELSE
      RAISE EXCEPTION 'Unexpected condition value %!', condition;
   END CASE;
END
$func$;

Simplified with a CASE construct while being at it, and added an ELSE clause. Adapt to your needs.

Call with:

SELECT * FROM public.testSpCrud(...);

Aside: all variable names of a plpgsql block are visible inside nested SQL DML commands. A variable named id is a problem waiting to happen. I suggest a safer naming convention, and / or table-qualify all column names in DML statements. One popular naming convention is to prepend variable names with an underscore. Like: _id.

And consider legal, lower-case identifiers in SQL and PL/pgSQL.

• Are PostgreSQL column names case-sensitive?