Postgresql function returns composite - how do I access composite values as separate columns?

Question

I have a Postgresql function which returns a composite type defined as (location TEXT, id INT). When I run "SELECT myfunc()", My output is a single column of type text, formatted as:

("locationdata", myid)

This is pretty awful. Is there a way to select my composite so that I get 2 columns back - a TEXT column, and an INT column?

Answer 1

Use:

SELECT * 
  FROM myfunc()

You can read more about the functionality in this article.

Answer 2

Answer has already been accepted, but I thought I'd throw this in:

It may help to think about the type of the data and where those types fit into an overall query. SQL queries can return essentially three types:

• A single scalar value
• A list of values
• A table of values

(Of course, a list is just a one-column table, and a scalar is just a one-value list.)

When you look at the types, you see that an SQL SELECT query has the following template:

SELECT scalar(s)
FROM table
WHERE boolean-scalar

If your function or subquery is returning a table, it belongs in the FROM clause. If it returns a list, it could go in the FROM clause or it could be used with the IN operator as part of the WHERE clause. If it returns a scalar, it can go in the SELECT clause, the FROM clause, or in a boolean predicate in the WHERE clause.

That's an incomplete view of SELECT queries, but I've found it helps to figure out where my subqueries should go.