3

I have a simple function that outputs 2 parameters (in this example word and part_of_speech).

CREATE OR REPLACE FUNCTION findword(out word1 text, out pos1 text) AS $$
BEGIN

SELECT word, partofspeech 
INTO word1, pos1
from content_word
ORDER BY RANDOM() 
LIMIT 1;

END;
$$ LANGUAGE plpgsql;

If I use

SELECT * FROM findword();

I get expected results.

But when I use 

SELECT (findword()).*

I get bizarre results: 

tight | v
jury  | j

as if word1 and pos1 were drawn from separate queries.

What am I doing wrong here?

Clémentine
  • 468
  • 1
  • 5
  • 16
  • 1
    What is the result you expect? (The one that is "fine")? The `create function` statement isn't correct, you need to specify a `returns` part which you didn't. Please [edit] the question and show us the **exact** definition of the function –  Oct 19 '16 at 10:46
  • 3
    Use `SELECT * FROM findword()` instead. PostgreSQL unfortunately does the expansion of `*` before any other optimization, so your query is equivalent with `SELECT (findword()).word1, (findword()).pos1` (where you can see clearly, that your function is executed twice). – pozs Oct 19 '16 at 11:03
  • Thanks @pozs this is exactly what I was wondering. – Clémentine Oct 19 '16 at 11:06
  • @pozs May I steal your points by posting your answer or will you do that? – Clodoaldo Neto Oct 19 '16 at 16:08
  • @ClodoaldoNeto I would rather not repeat an answer; the one I linked (and I think many more) contains enough information to solve OP's problem (which is [surprisingly hard to search for](http://stackoverflow.com/search?q=%5Bpostgresql%5D+star+expand)) – pozs Oct 20 '16 at 07:31
  • @pozs I don't think it's the same question as the one you mentioned. I edited to make this clear. My question is about why SELECT (somefunction()).* and select * from somefunction() give different results – Clémentine Oct 20 '16 at 15:57
  • Also: http://stackoverflow.com/questions/38297935/split-function-returned-record-into-multiple-columns/38297985#38297985 – Erwin Brandstetter Dec 29 '16 at 02:53

0 Answers0