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

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Seref Arikan <serefarikan(at)gmail(dot)com>
Cc: PG-General Mailing List <pgsql-general(at)postgresql(dot)org>
Subject: Re: How can I create null value from function call with no results?
Date: 2014-07-30 18:54:11
Message-ID: 11466.1406746451@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Kevin Goess 2014-07-30 18:57:43 Re: free RAM not being used for page cache
Previous Message Merlin Moncure 2014-07-30 18:49:32 Re: free RAM not being used for page cache