SELECT FROM a function returning a record with arbirary number of columns
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 type instead. Besides the workarounds @Chris provided there are a several other ways to declare the return type. I wrote comprehensive related answers recently:
- 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 already. I remember answering a bunch of them.
Try a search!
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).field3FROM(SELECT my_function(*) FROM sometable) t
You have a few options here:
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.
Return an XML document and parse it in the application.
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.