Where are user defined functions saved in PostgreSQL?

Question

To clarify the issue I am having, I am writing pgtap unit tests on DB and one of my functions returns a table. However, the pgtap function function_returns does not have a definition for table check and their columns (names and data types).

These are all the overloads of the function:

SELECT function_returns( :schema, :function, :args, :type, :description );
SELECT function_returns( :schema, :function, :args, :type );
SELECT function_returns( :schema, :function, :type, :description );
SELECT function_returns( :schema, :function, :type );
SELECT function_returns( :function, :args, :type, :description );
SELECT function_returns( :function, :args, :type );
SELECT function_returns( :function, :type, :description );
SELECT function_returns( :function, :type );

You may read the full definition and what each parameter stands for in the official documentation

Basically the issue is that :type does not work for table.

So I am wondering where these user defined functions are saved in Postgres (trying to write my own version and hopefully contribute to the pgtap project).

After digging in the source code I found the function used in the background to check for this, it's called _func_compare

CREATE OR REPLACE FUNCTION _func_compare( NAME, NAME, NAME[], anyelement, anyelement, TEXT)
RETURNS TEXT AS $$
    SELECT CASE WHEN $4 IS NULL
      THEN ok( FALSE, $6 ) || _nosuch($1, $2, $3)
      ELSE is( $4, $5, $6 )
      END;
$$ LANGUAGE SQL;

And I can't find out how the author was checking for valid return definition of the function.

Answer 1

You can get the definition of a function from the pg_proc system catalog.

If prorettype is contains the special record type, the actual return types are the elements in proallargtypes where the corresponding proargmodes entry is t, o or b.

This query should give you all functions maned test together with their object ID, the input types and the result types:

SELECT p.oid,
       p.proname,
       p.proargtypes::regtype[] AS input_types,
       /* only list output arguments */
       array_agg(a.type::regtype ORDER BY a.n)
          FILTER (WHERE a.mode IN ('t', 'o', 'b'))
          AS result_types
FROM pg_proc AS p
   CROSS JOIN LATERAL (/* get all arguments for table functions */
                       SELECT *
                       FROM unnest(p.proallargtypes, p.proargmodes) WITH ORDINALITY
                       UNION
                       /* get the return type for other functions */
                       SELECT p.prorettype, 'o', 1
                       WHERE p.proallargtypes IS NULL
                      ) AS a(type,mode,n)
WHERE p.proname = 'test'
GROUP BY p.oid, p.proname, p.proargtypes;