Re: Nested query performance issue

On Thu, Apr 9, 2009 at 7:29 AM, Heikki Linnakangas
<heikki(dot)linnakangas(at)enterprisedb(dot)com> wrote:
> CREATE FUNCTION topnscores(game_id int , n int) RETURNS SETOF score LANGUAGE
> SQL AS $$
> SELECT * FROM score s WHERE s.game_id = $1 ORDER BY score DESC LIMIT $2
> $$;
>
> SELECT (sub.ts).id, (sub.ts).score, (sub.ts).game_id
> FROM (SELECT topnscores(g.id, 5) ts FROM game g) sub;

The inner query:

SELECT topnscores(g.id, 5) ts FROM game g

http://www.postgresql.org/docs/8.3/static/xfunc-sql.html says this is
deprecated (though no deprecation warning is being generated):

> Currently, functions returning sets can also be called in the select list of a query. For each row that the query generates by itself, the function returning set is invoked, and an output row is generated for each element of the function's result set. Note, however, that this capability is deprecated and might be removed in future releases.

It doesn't say how else to write this, though, and it's not obvious to
me. "SELECT ts.* FROM topnscores(g.id, 5) AS ts, game g" doesn't work
("function expression in FROM cannot refer to other relations of same
query level"). Is there an equivalent way to do this so I won't have
deprecation looming over my back? I'm likely to become very dependent
on this pattern.

--
Glenn Maynard