Thanks Tom, The function can return multiple rows. It is a very simplified version of a function that is used in the context of an EAV design.
It should return 0 or more rows that match the criteria that is calculated in the function.
Originally I had a left outer join from the table that I'm using in the SELECT here to a subquery. The problem is, postgres 9.3 chooses an inefficient query plan when I do that (and this is all in a quite large query). If I replace the LEFT OUTER JOIN + subquery with the function call approach, the overall query runs a lot faster. So it is a workaround for performance reasons, though it leaves a bad taste in my mouth as well :(
Best regards Seref
On Wed, Jul 30, 2014 at 7:54 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Seref Arikan <serefarikan@gmail.com> writes: > I want to call a function using a column of a table as the parameter and > return the parameter and function results together. > The problem is, when the function returns an empty row my select statement > that uses the function returns an empty row as well.
This function isn't actually returning an empty row; it's returning no rows, which is possible because RETURNS TABLE is really RETURNS SETOF some-record-type. It's not entirely clear what you're trying to accomplish, so the first thing is to get clear on that. Perhaps you want it to always return one row? If so, don't use the TABLE notation (just list some OUT parameters instead). If you actually do want it to return zero rows, then the problem is not with the function but with the query you're using it in. Set-returning functions in a SELECT's targetlist are often a bad idea.
