Separating record returned from function in postgres

I've got a function that returns two parameters as an anonymous composite type via output parameters.

I can access the individual columns with a query like this:

# select * from guess_user('Joe','Bloggs');
 confidence | matchid 
------------+---------
   0.142857 |    1121

Now I want to combine the output from this function with some data:

# select firstname,lastname from users limit 5;
 firstname | lastname 
-----------+----------
 Adam      | Smith
 Amy       | Peters
 Annette   | Bloggs
 Annie     | Mills
 Amanda    | Hibbins

I am looking for a query that will output the following:

 firstname | lastname | confidence | matchid 
-----------+----------+------------+---------
 Adam      | Smith    |            | 
 Amy       | Peters   |            | 
 Annette   | Bloggs   |            | 
 Annie     | Mills    |            | 
 Amanda    | Hibbins  |            | 

With the confidence and matchid columns filled out using the results of calling guess_user with the names from that row.

My current closest effort is:

# select firstname, lastname, guess_user(firstname, lastname) from users limit 5;

Which returns:

 firstname | lastname  |  guess_user   
-----------+-----------+---------------
 Angela    | Abbott    | (0.285714,3)
 Amy       | Allan     | (0.285714,4)
 Annette   | Allison   | (0.285714,5)
 Annie     | Ashworth  | (0.285714,6)
 Amanda    | Baird     | (0.285714,7)

Is there a way to split the guess_user output into separate columns?

Answers


combining depesz and fazal's answers this seems to work:

select firstname, lastname, (guess_user(firstname, lastname)).*
from users
limit 5

Simply make it like this:

select firstname, lastname, x.confidence, x.matchid
from 
(
select firstname, lastname, guess_user(firstname, lastname) as x
from users
limit 5
) q;

You need to change your function to return a set - the last example is identical to the functionality you are asking for.


Unless someone comes along and corrects me, I believe that the answer is that it's currently not possible in 8.3 but can be done in Postgres 8.4.

http://www.depesz.com/index.php/2008/11/03/waiting-for-84-pl-srf-functions-in-selects/


You may need to parenthesize the "x" in depesz's solution, to distinguish the composite record value from a table, so you don't get the message:

missing FROM-clause entry for table "x"

At least I do on 9.0.2.


Need Your Help

How disable / remove android activity label and label bar?

android

Is there anyway to remove activity label bar and label itself which is set by default from application label?

Xcode and deprecated warnings not always working

objective-c xcode ios5

I have a strange behavior with Xcode (4.3.2) and deprecated warnings, I would like to understand why.