Postgres Functions: Getting the Return Table Column Details

Question

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.

One solution I think may always work for me is a bit goofy, but I can — Kirk Fleming
– Kirk Fleming, Commented Jun 9, 2021 at 19:19
Then you should post that solution as an answer to help future questioners having the same issue. And perhaps the community can make it less goofy. — Belayer
– Belayer, Commented Jun 9, 2021 at 19:22

Laurenz Albe · Accepted Answer · 2021-06-10 07:18:18Z

3

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;

edited Jun 10, 2021 at 7:18

answered Jun 9, 2021 at 21:19

Laurenz Albe

257k22 gold badges313 silver badges389 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Kirk Fleming Over a year ago

Took me 90 min or more to get this to return anything (yes, with the correct proname), but that single query has more information about Postgres in it that my previous total knowledge. Secret for my particular situation was to remove the 2nd WHERE constraint--all values are 't' so I don't understand why doing so changed anything. Casting the types as regtype to decode them is also not something I would have ever looked for--I was looking for some tabular source of 'types' to find the type name. Solved--now just a matter of understanding why (and the catalog). Nice.

Laurenz Albe Over a year ago

Are you saying that proargmodes contains t?

Kirk Fleming Over a year ago

Yes. All of the columns associated with the RETURNS TABLE section are 't', and the IN parameter is 'i'. I've been unable to find any reference at all to the i, o, b and t codes--but I assume for now they are in, out, both and table.

Kirk Fleming Over a year ago

My apology...the specific function I used for testing has one IN parameter only, no INOUT, and RETURNS TABLE with about 20 or so columns. It is these ~20 output table columns that are tagged in pg_proc as 't' mode.

Laurenz Albe Over a year ago

Oh, my oversight, thanks for the explanation. I have fixed the query.

Collectives™ on Stack Overflow

Postgres Functions: Getting the Return Table Column Details

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related