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.

• Chessbrain: @LaurenzAlbe does not specify which schema it belongs to (2 functions have the same name in different schemas with different purposes). Also function return is not defined anywhere in this table, or am I missing it?
• Chessbrain: @LaurenzAlbe All I see are numbers and there's no documentation anywhere on their meaning (I can probably decipher them myself by making different return type functions and see the corresponding number). Also it doesn't specify what columns are being returned. Also, pronamespace is a number again?
• Chessbrain: @LaurenzAlbe I truly apologize, didn't notice the reference :( Although this does tell me returns table = record in pg_type. But from what I see there's no way to see complete return (all columns, their type and name in return). I see I can find this info about input arguments (proargnames,proargtypes) but not output? proargnames also includes both in and out defined parameters so it's not ideal. I am hoping I didn't miss anything like last time :(
• user1822: If there are only number in the source code, then the original author did not use parameter names, only placeholders. See this example: dbfiddle.uk/… Postgres can't return information that wasn't available when function was created.

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;

• user1822: The function arguments can formatted quite nicely using pg_catalog.pg_get_function_arguments(p.oid) which removes the need for the derived table (it would mix in and out parameters though)
• Chessbrain: I am going to assume there's no way to see name of columns unless return is defined as out <name> <type> in function definition. So if I do return table (<list of column>) it's not possible to see them
• Laurenz Albe: Everything is stored in the metadata. That would be the proargnames column.