CODEWITHSUNDEEP
postgresql
Kirk Fleming
Kirk Fleming

Reputation: 517

Postgres Functions: Getting the Return Table Column Details

I feel the need to get the column names and data types of the table returned by any function that has a 'record' return data type, because...

A key process in an existing SQL Server-based system makes use of a stored procedure that takes a user-defined function as a parameter. An initial step gets the column names and types of the table returned by the function that was passed as a parameter.

In Postgres 13 I can use pg_proc.prorettype and the corresponding pg_type to find functions that return record types...that's a start. I can also use pg_get_function_result() to get the string containing the information I need. But, it's a string, and while I ultimately will have to assemble a very similar string, this is just one application of the info. Is there a tabular equivalent containing (column_name, data_type, ordinal_position), or do I need to do that myself?

Is there access to a composite data type the system may have created when such a function is created?

One option that I think will work for me, but I think it's a little weird, is to:

> create temp table t as select * from function() limit 0;

then look that table up in info_schema.columns, assemble what I need and drop the temp table...putting all of this into a function.

Upvotes: 1

Views: 498

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246858

You can query the catalog table pg_proc, which contains all the required information:

SELECT coalesce(p.na, 'column' || p.i),
       p.ty::regtype,
       p.i
FROM pg_proc AS f
   CROSS JOIN LATERAL unnest(
                         coalesce(f.proallargtypes, ARRAY[f.prorettype]),
                         f.proargmodes,
                         f.proargnames
                      )
                      WITH ORDINALITY AS p(ty,mo,na,i)
WHERE f.proname = 'interval_ok'
  AND coalesce(p.mo, 'o') IN ('o', 't')
ORDER BY p.i;

Upvotes: 3

Related Questions