CODEWITHSUNDEEP
postgresqlstored-procedurestypesrecordplpgsql
flash.tato
flash.tato

Reputation: 81

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

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?

Upvotes: 8

Views: 8793

Answers (5)

NoelProf
NoelProf

Reputation: 955

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

Or more simply like

SELECT (my_function('input thing')).field1

Upvotes: 5

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656794

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:

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

Upvotes: 7

fmpdmb
fmpdmb

Reputation: 1414

I'm not certain that I follow what you're after, but does this work?

SELECT (my_function(97)).my_column

Upvotes: 0

Chris Travers
Chris Travers

Reputation: 26464

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.

Upvotes: 2

Karlson
Karlson

Reputation: 3048

In order to return a "set of columns" you will have define a return type as TABLE or SETOF in which case you actually return a SET of records which you should be able to SELECT from.

For more information about functions returning SETOF take a look at this link to documentation

Upvotes: 0

Related Questions