SELECT FROM a function returning a record with arbirary number of columns

Question

I'm using PostgreSQL database.

I've a my plpgsql FUNCTION that returns a single record with an arbitrary number of columns.
Due to this arbitrariness I would need to use something like:

SELECT * FROM my_function(97)

But this doesn't work as Postgres gives me the following error:

a column definition list is required for functions returning "record"

But if I do:

SELECT my_function(97)

I can see the expected result but encapsulated in a single column.

Is there a way to fetch the expected result as a set of columns as intended by the function and not a single column encapsulating all of them?

Answer 1

When a function just RETURNS record or SETOF record (and no OUT parameters to go with it), PostgreSQL does not know the names and types of its elements and you are required to provide a column definition list with every call.

Avoid that if at all possible and return a well known (row) type instead. There are a several ways to declare the return type. See:

• PostgreSQL: ERROR: 42601: a column definition list is required for functions returning "record"
• Refactor a PL/pgSQL function to return the output of various SELECT queries

There are quite a few related questions on SO. Try a search!

Answer 2

When using a set returning function (setof) in the select list, on the left hand side of the FROM, the function returns a composite type. Using a function in the select list can be hard to avoid when using a table as input to a function.

A way to SELECT items from a single column of composite type follows:

SELECT
  (my_function).field1,
  (my_function).field2,
  (my_function).field3
FROM
(SELECT my_function(*) 
 FROM sometable) t

Answer 3

You have a few options here:

1. Return a REFCURSOR and fetch from that cursor in the application. Note you can actually return multiple REFCURSORS if you need to return multiple result sets.

2. Return an XML document and parse it in the application.

3. Use a bunch of OUT variables, return RECORD, and determine which of these to select from

The basic problem is that the actual return results need to be known at planning time so you can't just return an arbitrary number of columns. The planner needs to know what is going to be returned.