Separating record returned from function in postgres

Question

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?

In case it's helpful for anyone wanting to experiment, here's a function that will return the same record type as the one I'm using: create or replace function guess_user ( firstname varchar, lastname varchar, OUT confidence real, OUT matchid integer ) as $$ BEGIN matchid := 1; confidence := 0.5; RETURN; END; $$ LANGUAGE plpgsql; — simoncoggins
– simoncoggins, Commented Dec 11, 2009 at 4:25
Would it be possible to implement this as a view instead? developer.postgresql.org/pgdocs/postgres/sql-createview.html — OMG Ponies
– OMG Ponies, Commented Dec 11, 2009 at 5:05

Emery Lapinski · Accepted Answer · 2013-07-11 17:29:55Z

14

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

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

edited Jul 11, 2013 at 17:29

answered Jul 5, 2013 at 20:58

Emery Lapinski

1,6621 gold badge19 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Emery Lapinski Over a year ago

To add a little more information, per postgresql.org/docs/9.3/static/rowtypes.html "To access a field of a composite column, one writes a dot and the field name, much like selecting a field from a table name. In fact, it's so much like selecting from a table name that you often have to use parentheses to keep from confusing the parser."

user80168 · Accepted Answer · 2009-12-13 20:29:39Z

2

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;

answered Dec 13, 2009 at 20:29

user80168

1 Comment

Phat H. VU Over a year ago

You have to use parenthesis around to access composite type fields: select firstname, lastname, (x).confidence, (x).matchid Otherwise, get this error: "missing FROM-clause entry for table "x""

OMG Ponies · Accepted Answer · 2009-12-11 04:28:55Z

1

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

answered Dec 11, 2009 at 4:28

OMG Ponies

334k85 gold badges536 silver badges508 bronze badges

2 Comments

OMG Ponies Over a year ago

RETURNS SETOF record to indicate that the function returns multiple rows instead of just one. They aren't tied to an existing table, you could return a TYPE instead if you preferred. wiki.postgresql.org/wiki/…

simoncoggins Over a year ago

Thanks, I gave that a go but I still couldn't seem to put the function in the right place to get it to work. This page: depesz.com/index.php/2008/11/03/… Seems to suggest it's a limitation of postgres, which is solved on 8.4 (I'm running 8.3.8).

simoncoggins · Accepted Answer · 2009-12-13 19:33:48Z

0

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/

answered Dec 13, 2009 at 19:33

simoncoggins

3984 silver badges12 bronze badges

Comments

Fazal Majid · Accepted Answer · 2010-12-30 20:12:28Z

0

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.

answered Dec 30, 2010 at 20:12

Fazal Majid

7287 silver badges13 bronze badges

Collectives™ on Stack Overflow

Separating record returned from function in postgres

5 Answers 5

1 Comment

1 Comment

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

1 Comment

1 Comment

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related