I am working on postgres 12. I have some functions defined that have TABLE(...) as return values. I know how to query the information_schema and/or pg_proc to get the list of arguments of the function given its name and schema. I would like to do something similar for the return type when it is a TABLE, that is, to find a query, that when the function returns a set of records, will return a list of the ordinal position and oid (or name) for the columns of the return table.
Is that possible?
EDIT: I know of pg_get_function_result() which returns the text of the return definition as it is written in the function definition but I would have to parse that and i wonder if there is a simpler way.
pg_proc.prorettype will contain the OID of the table's type, so you can use:
select prorettype::regtype::text
from pg_proc
where proname = 'your_function';
to get the name of the type - which is also the name of the table.
To get all the columns, you can join pg_proc with pg_class to get the table's oid and then use that to find the columns.
select col.attname, col.attnum, format_type(col.atttypid, col.atttypmod) as data_type
from pg_attribute col
where not attisdropped
and attnum > 0
and attrelid in (select tbl.oid
from pg_class tbl
join pg_proc p on p.prorettype = tbl.reltype
where p.proname = 'your_function_name_here')
order by col.attnum
If you need to get the column names returned by a function defined as returns table(), they are available in the array pg_proc.proargnames for those that are defined as "out" parameters through proargmodes
select t.column_name, t.arg_type::regtype::text, t.col_num
from pg_proc p
cross join unnest(proargnames, proargmodes, proallargtypes) with ordinality as t(column_name, arg_mode, arg_type, col_num)
where p.proname = 'your_function_name_here'
and t.arg_mode = 't'
order by t.col_num;