0

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.

4
  • @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
    Jan 28, 2021 at 16:02
  • @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
    Jan 28, 2021 at 19:14
  • @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 :(
    – Chessbrain
    Jan 28, 2021 at 21:04
  • 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.
    – user1822
    Jan 28, 2021 at 22:27

1 Answer 1

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;
3
  • 1
    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)
    – user1822
    Jan 29, 2021 at 7:01
  • 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
    – Chessbrain
    Jan 29, 2021 at 19:52
  • Everything is stored in the metadata. That would be the proargnames column. Jan 30, 2021 at 2:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.