Re: How can I create null value from function call with no results?

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(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Seref Arikan <serefarikan(at)gmail(dot)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.
>
> regards, tom lane
>