CODEWITHSUNDEEP
postgresqlpostgresql-9.4information-schema
hooblei
hooblei

Reputation: 3240

How to read a function return table definition from pg_catalog or information_schema

I would like to read and process the column definitions of a functions return table type ... and also detect if the functions return type is a table.

I found the columns in information_schema.parameters but I can not distinguish if it is a OUT marked argument or a return table column definition.

In pg sources I noticed a "argmode" or "PROARGMODE" but I'm not aware how to find this info within information_schema or pg_catalog.

I'm aware that one could parse the result of pg_catalog.pg_get_function_result().

pg 9.4

Upvotes: 1

Views: 1008

Answers (1)

klin
klin

Reputation: 121754

There are two columns in the pg_proc catalog: proargtypes and proallargtypes. The first contains the data types of the function input arguments, while the second - the data types of all arguments. To get only the types of output arguments you should get a slice of proallargtypes skipping elements of proargtypes:

create or replace function function_return_types(p pg_proc)
returns oid[] language sql as $$
    select p.proallargtypes[array_length(p.proargtypes, 1)+ 1 : array_length(p.proallargtypes, 1)]
$$;

An example function and the query retrieving its return types:

create or replace function test_function (int, date, text)
returns table (i int, d date, t text)
language sql as $$
    select $1, $2, $3;
$$;

select proname, function_return_types(p)
from pg_proc p
where proname = 'test_function';

    proname    | function_return_types 
---------------+-----------------------
 test_function | {23,1082,25}
(1 row)

The function returns array of oids. You can use it to get the types names:

create or replace function function_return_type_names(p pg_proc)
returns name[] language sql as $$
    select array_agg(typname)
    from (
        select typname
            from unnest(function_return_types(p)) with ordinality u
            join pg_type t on t.oid = u
            order by ordinality
        ) s
$$;

select proname, function_return_type_names(p)
from pg_proc p
where proname = 'test_function';

    proname    | function_return_type_names 
---------------+----------------------------
 test_function | {int4,date,text}
(1 row)

The above answer is old and a bit outdated. In Postgres 14 I am using this function:

create or replace function function_out_args_types(p pg_proc)
returns name[] language sql as $$
    select array_agg(t::regtype order by o)
    from unnest(
        p.proallargtypes[p.pronargs+ 1 :]   
    ) with ordinality a(t, o)
$$;

Example of use:

select 
    proname as function_name,
    function_out_args_types(p),
    proretset as srf,
    prorettype::regtype as return_type
from pg_proc p
where proname = 'test_function';

 function_name | function_out_args_types | srf | return_type
---------------+-------------------------+-----+-------------
 test_function | {integer,date,text}     | t   | record
(1 row)

srf - function returns set of rows.

Upvotes: 3

Related Questions