Returning values for Stored Procedures in PostgreSQL

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.

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 PL/pgSQL code, you have to actively dismiss results if you don't want them. See:

• SELECT raises exception in PL/pgSQL function

-
A PROCEDURE (Postgres 11 or later) returns a single row if it has any arguments with the INOUT mode (or OUT mode since in Postgres 14). zsheep provided an example.

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

• Use PL/pgSQL in PostgreSQL outside of a stored procedure or function?

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.

In a PL/pgSQL procedure, assign to the parameter with the INTO keyword:

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

In an SQL procedure the last command returning values determines the return value:

CREATE PROCEDURE lang_sql_demo(INOUT _val text DEFAULT null)
  LANGUAGE sql AS
$proc$
SELECT val FROM tbl WHERE id = 2;
$proc$;

Similar in a standard SQL procedure:

CREATE OR REPLACE PROCEDURE lang_std_sql_demo(INOUT _val text DEFAULT null)
  LANGUAGE sql
BEGIN ATOMIC
SELECT val FROM tbl WHERE id = 2;
END;

See:

• What does BEGIN ATOMIC mean in a PostgreSQL SQL function / procedure?

db<>fiddle here

Related:

• Struggling to create a "stored procedure" beyond INSERT

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

StackExchange Database Administrators Q#257596, answer score: 13