Geek Answers Handbook

The Postgresql function returns the composition - how do I access composite values ​​as separate columns?

I have a Postgresql function that 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 to get 2 columns back - TEXT column and INT column?

+4
source share
2 answers

Using:

 SELECT * FROM myfunc()

You can learn more about the functionality in this article .

+6
source

The answer has already been accepted, but I thought I would throw it in:

This can help think about the data type and where these types fit into the general query. SQL queries can return essentially three types:

  • Single scalar value
  • List of values
  • Value table

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

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

 SELECT scalar(s) FROM table WHERE boolean-scalar

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

This is an incomplete representation of SELECT queries, but I found that it helps to figure out where my subqueries should go.

+2
source

More articles:


All Articles