Is there any formal difference at all between PostgreSQL functions with OUT parameters and with TABLE results?

Question

Consider these two PostgreSQL functions:

CREATE OR REPLACE FUNCTION f_1 (v1 INTEGER, v2 OUT INTEGER)
AS $$
BEGIN
    v2 := v1;
END
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION f_2 (v1 INTEGER)
RETURNS TABLE(v2 INTEGER)
AS $$
BEGIN
    v2 := v1;
END
$$ LANGUAGE plpgsql;

In any "ordinary" procedural SQL language (e.g. Transact-SQL), the two types of functions would be quite different. f_1 would actually be a procedure, whereas f_2 would be a table-valued function. In SQL Server, the latter is returned from INFORMATION_SCHEMA.ROUTINES like so:

SELECT r.routine_schema, r.routine_name
FROM   information_schema.routines r
WHERE  r.routine_type = 'FUNCTION'
AND    r.data_type = 'TABLE'

In PostgreSQL, this doesn't work, however. The following query shows that there is essentially no difference between the signatures of f_1 and f_2:

SELECT r.routine_name, r.data_type, p.parameter_name, p.data_type
FROM   information_schema.routines r
JOIN   information_schema.parameters p
USING (specific_catalog, specific_schema, specific_name);

The above yields:

routine_name | data_type | parameter_name | data_type
-------------+-----------+----------------+----------
f_1          | integer   | v1             | integer
f_1          | integer   | v2             | integer
f_2          | integer   | v1             | integer
f_2          | integer   | v2             | integer

Things don't get much better when I have multiple columns returned from the functions, in case of which I don't even have a "formal" return type anymore. Just record:

CREATE OR REPLACE FUNCTION f_3 (v1 INTEGER, v2 OUT INTEGER, v3 OUT INTEGER)
AS $$
BEGIN
    v2 := v1;
END
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION f_4 (v1 INTEGER)
RETURNS TABLE(v2 INTEGER, v3 INTEGER)
AS $$
BEGIN
    v2 := v1;
END
$$ LANGUAGE plpgsql;

... I'll get:

routine_name | data_type | parameter_name | data_type
-------------+-----------+----------------+----------
f_3          | record    | v1             | integer
f_3          | record    | v2             | integer
f_3          | record    | v3             | integer
f_4          | record    | v1             | integer
f_4          | record    | v2             | integer
f_4          | record    | v3             | integer

If coming from other databases, clearly the intent of the lexical signature is quite different. As an Oracle person, I expect PROCEDURES to have side-effects, whereas FUNCTIONS don't have any side-effects (unless in an autonomous transaction) and can be safely embedded in SQL. I know that PostgreSQL cleverly treats all functions as tables, but I don't think it's a good idea to design OUT parameters as table columns in any query...

My question is:

Is there any formal difference at all between the two ways to declare functions? If there is, how can I discover it from the INFORMATION_SCHEMA or from the PG_CATALOG?

Answer 1

\df public.f_* does this

select
    n.nspname as "Schema",
    p.proname as "Name",
    pg_catalog.pg_get_function_result(p.oid) as "Result data type",
    pg_catalog.pg_get_function_arguments(p.oid) as "Argument data types",
    case
        when p.proisagg then 'agg'
        when p.proiswindow then 'window'
        when p.prorettype = 'pg_catalog.trigger'::pg_catalog.regtype then 'trigger'
        else 'normal'
    end as "Type"
from
    pg_catalog.pg_proc p
    left join
    pg_catalog.pg_namespace n on n.oid = p.pronamespace
where
    p.proname ~ '^(f_.*)$'
    and n.nspname ~ '^(public)$'
order by 1, 2, 4;

which returns this

                                          List of functions
 Schema | Name |       Result data type        |            Argument data types             |  Type  
--------+------+-------------------------------+--------------------------------------------+--------
 public | f_1  | integer                       | v1 integer, OUT v2 integer                 | normal
 public | f_2  | TABLE(v2 integer)             | v1 integer                                 | normal
 public | f_3  | record                        | v1 integer, OUT v2 integer, OUT v3 integer | normal
 public | f_4  | TABLE(v2 integer, v3 integer) | v1 integer                                 | normal
(4 rows)

To drop a function it is necessary to pass its input (IN and INOUT) arguments data types. Then I guess the function name and its input arguments data types do form its signature. And to change the returned data type it is necessary to first drop it and recreate.