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?
combining depesz and fazal's answers this seems to work:
select firstname, lastname, (guess_user(firstname, lastname)).*
from users
limit 5
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With