2

I have a SELECT from a function (with ordinary, not SETOF argument). However, I would like to call the function multiple times within the query (and then to aggregate the results), feeding it with a CTE.

Some (example) code:

CREATE TYPE MY_TYPE AS ( a integer, b integer );

CREATE OR REPLACE FUNCTION foo( foobar INTEGER ) 
RETURNS MY_TYPE AS $$
BEGIN
  -- do something, return MY_TYPE
END;  
$$ LANGUAGE plpgsql;

WITH x AS (
  SELECT a FROM bar WHERE b = 1
)
SELECT min(a) FROM foo( (SELECT a FROM x) ) f

This doesn't work, I get following error message:

ERROR:  more than one row returned by a subquery used as an expression

Now I wonder how to rewrite the query. I could loop over the CTE results, then call the function with one input value at a time, store results in a column, and finally find the minimum - this feels clumsy and should be quite slow. Another approach would be to wrap my function in another, set-returning and with array parameter. But this just pushes the problem away: I don't know how to simply wrap it.

What seems fine is to rewrite the function to the SETOF form. Still, this is a workaround, and learning how to call ordinary functions with more than one row returned by a query (if it is possible) would be better.

Pavel V.
  • 2,653
  • 10
  • 43
  • 74
  • I see you accepted an answer, but did you ever find a solution that actually let you pass multiple rows *into* a function? I've got a similar question at [How to pass a set of rows from one function into another?](http://stackoverflow.com/q/26937824/1281433) – Joshua Taylor Nov 15 '14 at 03:33
  • @JoshuaTaylor: I accepted it because the workaround worked quite well for me. I'll have a look at your question and think it over again, and if I find some solution, I'll post some answer (or answers) to my or your question. – Pavel V. Nov 19 '14 at 13:09
  • Well, as discussed in that question, I'm beginning to think that since DB queries are optimized so much, those functions may actually behave more like macros that get expanded in place, so it really might not be possible. We'll see, though... – Joshua Taylor Nov 19 '14 at 14:13

1 Answers1

0

Try this:

SELECT min(f.a)
  FROM bar  
  CROSS JOIN foo(bar.a) f
  WHERE bar.b = 1
Rimas
  • 5,904
  • 2
  • 26
  • 38
  • Current version of your answer is clearly better than mine, so I accept this one and delete my answer. Only value it adds is the note how to avoid the mistake you did in the first version of your post: the function call must be in JOIN, not directly in FROM clause, otherwise you get "missing FROM-clause entry" error. This is better as a comment than as a separate answer. – Pavel V. Sep 09 '14 at 06:58