I was reading this on PostgreSQL Tutorials:

In case you want to return a value from a stored procedure, you can use output parameters. The final values of the output parameters will be returned to the caller.

And then I found a difference between function and stored procedure at DZone:

Stored procedures do not return a value, but stored functions return a single value

Can anyone please help me resolve this.

If we can return anything from stored procedures, please also let me know how to do that from a SELECT statement inside the body.

If I am wrong somewhere please inform.

  • 2
    If you want to return something use a function, procedures aren't meant to return something. DZone is wrong, a function can return a result set, not just a single value – a_horse_with_no_name Jan 16 at 18:55
  • Thank you so much Sir Erwin Brandstetter, for improving the question's formatting and syntax, making it more readable and ethical, really appreciated and too thank you for devoting your time reading and improving it, Thank you once again. – purav desai Jul 22 at 18:11

Both your sources are plain wrong.

  • A FUNCTION basically always returns something. void at the minimum, a single value, a row, or a set of rows ("set-returning function", a.k.a. "table-function") - the only variant that can return nothing, i.e. no row. Called from within plpgsql code, you have to actively dismiss results if you don't want them. See:

  • A PROCEDURE (Postgres 11 or later) returns a single row if it has any arguments with the INOUT mode. zsheep already provided an example.

Consider a DO statement to run ad-hoc plpgsql code without passing or returning anything.

To address your core question:

If we can return anything from stored procedures, please also let me know how to do that from a SELECT statement inside the body.

The same way as in functions, as that's in the realm of general PL/pgSQL: assign to the parameter with the INTO keyword:

CREATE PROCEDURE assign_demo(INOUT _val text DEFAULT null)
  LANGUAGE plpgsql AS
   SELECT val FROM tbl WHERE id = 2
   INTO _val;                              -- !!!

db<>fiddle here


| improve this answer | |

Procedure are a new thing in Postgresql as of version 11, there are two big differences between Procedures and Functions

One Procedures can issue a commit or rollback and keep processing, Functions can not issue a commit or rollback.

Functions create an implicit transaction any exception that occurs will cause a rollback, unless there is an Exception Block

Procedure can return a value via INOUT argument see create functions how that works

create procedure increase(inout i integer )
LANGUAGE 'plpgsql'
AS $$
    i = i+1;
end ;

call increase(1);

2nd biggest difference is

select increase () -- fails with an error 
-- must use CALL to use a procedure
call increase (3) ;

Unless there is a need to commit or rollback use Functions, there are more features for calling and return values...

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.