On 5th of October 2020, Peter Eisentraut committed patch:

Support for OUT parameters in procedures 
 
Unlike for functions, OUT parameters for procedures are part of the
signature.  Therefore, they have to be listed in pg_proc.proargtypes
as well as mentioned in ALTER PROCEDURE and DROP PROCEDURE.
 
Reviewed-by: Andrew Dunstan <andrew.dunstan@2ndquadrant.com>
Reviewed-by: Pavel Stehule <pavel.stehule@gmail.com>
Discussion: https://www.postgresql.org/message-id/flat/2b8490fe-51af-e671-c504-47359dc453c5@2ndquadrant.com

This is huge.

Procedures were introduced in PostgreSQL 11, and they solved problem of having logic in DB that can span multiple transactions.

But they never could return data. You called them, and not selected from them. So, while you could use RAISE NOTICE to return data in some way, you couldn't then use this data in normal queries without going through some serious hoops.

This is no longer the case. Now, with this new patch, we can return data from procedure.

Let's consider very simple example:

=$ CREATE PROCEDURE test( IN elements INT4, OUT created int4[], OUT failed int4[] ) LANGUAGE plpgsql AS $$
DECLARE
    i int4;
BEGIN
    FOR i IN 1 .. elements loop
        IF random() < 0.5 THEN
            failed := failed || i;
        ELSE
            created := created || i;
        END IF;
    END loop;
END;
$$;

This procedure will iterate over given number of elements, for every element (integer in range 1..GIVEN_NUMBER), will check (based on random) if it should go to “created" or “failed" arrays.

Very simple, completely useless, but it works:

=$ CALL test(15, '{}'::int4[], '{}'::int4[]);
       created        |       failed
----------------------+--------------------
 {1,2,4,6,8,11,14,15} | {3,5,7,9,10,12,13}
(1 ROW)

One thing – values given to OUT parameters don't matter – values of the output are preset to NULL before executing procedure body.

=$ CALL test(2, '{10,11}'::int4[], '{12,13}'::int4[]);
 created | failed 
---------+--------
 {2}     | {1}
(1 ROW)

This is pretty awesome, thanks to all involved.