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.

Answer 2

It appears that the pg_catalog.pg_proc.proretset flag contains a hint about whether the function returns a set (i.e. a table):

SELECT   r.routine_name, r.data_type, p.parameter_name, 
         p.data_type, pg_p.proretset
FROM     information_schema.routines r
JOIN     information_schema.parameters p
USING   (specific_catalog, specific_schema, specific_name)
JOIN     pg_namespace pg_n
ON       r.specific_schema = pg_n.nspname
JOIN     pg_proc pg_p
ON       pg_p.pronamespace = pg_n.oid
AND      pg_p.proname = r.routine_name
WHERE    r.routine_schema = 'public'
AND      r.routine_name IN ('f_1', 'f_2', 'f_3', 'f_4')
ORDER BY routine_name, parameter_name;

The above would yield:

routine_name | data_type | parameter_name | data_type | proretset
-------------+-----------+----------------+-----------+----------
f_1          | record    | v1             | integer   | f
f_1          | record    | v2             | integer   | f
f_2          | record    | v1             | integer   | t
f_2          | record    | v2             | integer   | t
f_3          | record    | v1             | integer   | f
f_3          | record    | v2             | integer   | f
f_3          | record    | v3             | integer   | f
f_4          | record    | v1             | integer   | t
f_4          | record    | v2             | integer   | t
f_4          | record    | v3             | integer   | t

INFORMATION_SCHEMA.COLUMNS emulation

For what it's worth and in case someone needs this crazy thing, here's the beautiful query I came up with to emulate SQL Server's nice INFORMATION_SCHEMA.COLUMNS implementation that returns table-valued function columns (which is what we really needed when supporting table-valued functions in jOOQ's code generator):

SELECT 
  p.proname AS TABLE_NAME,
  columns.proargname AS COLUMN_NAME, 
  ROW_NUMBER() OVER(PARTITION BY p.oid ORDER BY o.ordinal) AS ORDINAL_POSITION,
  format_type(t.oid, t.typtypmod) AS DATA_TYPE,
  information_schema._pg_char_max_length(t.oid, t.typtypmod) AS CHARACTER_MAXIMUM_LENGTH,
  information_schema._pg_numeric_precision(t.oid, t.typtypmod) AS NUMERIC_PRECISION,
  information_schema._pg_numeric_scale(t.oid,t.typtypmod) AS NUMERIC_SCALE,
  not(t.typnotnull) AS IS_NULLABLE
FROM pg_proc p,
LATERAL generate_series(1, array_length(p.proargmodes, 1)) o(ordinal),
LATERAL (
  SELECT 
    p.proargnames[o.ordinal], 
    p.proargmodes[o.ordinal], 
    p.proallargtypes[o.ordinal]
) columns(proargname, proargmode, proargtype),
LATERAL (
  SELECT pg_type.oid oid, pg_type.* 
  FROM pg_type 
  WHERE pg_type.oid = columns.proargtype
) t
WHERE p.proretset
AND proargmode = 't'
AND p.proname LIKE 'f%';

The above nicely returns (column names shortened for SO):

table_name | column_name  | ordinal | data_type | length | precision | scale | nullable
f_2        | v2           |       1 | integer   |        |        32 |     0 | t
f_4        | v2           |       1 | integer   |        |        32 |     0 | t
f_4        | v3           |       2 | integer   |        |        32 |     0 | t