CODEWITHSUNDEEP
sqlpostgresql
simoncoggins
simoncoggins

Reputation: 398

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?

Upvotes: 10

Views: 6534

Answers (5)

Emery Lapinski
Emery Lapinski

Reputation: 1662

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

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

Upvotes: 14

Fazal Majid
Fazal Majid

Reputation: 728

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.

Upvotes: 0

user80168
user80168

Reputation:

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;

Upvotes: 2

simoncoggins
simoncoggins

Reputation: 398

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/

Upvotes: 0

OMG Ponies
OMG Ponies

Reputation: 332591

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

Upvotes: 1

Related Questions