Re: how to return 0 rows in function

On Fri, 18 May 2007, Richard Huxton wrote:

> Tomas Macek wrote:
>> Hi, I have simplified function like this:
>>
>> ----------------
>> CREATE OR REPLACE FUNCTION f(varchar) RETURNS varchar AS $FUNC$
>> DECLARE
>> addr ALIAS FOR $1;
>> BEGIN
>> -- return NULL;
>> -- return '';
>> END
>> $FUNC$ LANGUAGE 'plpgsql';
>> -----------------
>>
>> This function is returning varchar and it always returns at least one row.
>> How can I make it to return 0 rows? Is it possible or not and how?
>
> It's not returning one row, it's returning a single scalar value (a varchar).
> SELECT f('x') will return one row, because a SELECT statement returns a set
> (well, actually a bag) of results.
>
>> Returning NULL does not help (return NULL string in 1 row). Not to return a
>> value leads to error output.
>
> If you want to return multiple results (in your case zero) you'll need to
> return a set of them:
>
> CREATE FUNCTION f2(varchar) RETURNS SETOF varchar AS $$
> DECLARE
> BEGIN
> IF $1 = 'a' THEN
> RETURN NEXT 'hello';
> END IF;
> RETURN;
> END
> $$ LANGUAGE plpgsql;
>
> SELECT * FROM f2('b');
> f2
> ----
> (0 rows)
>
> As you can see you need to call the function in set-returning context now.
>
> Does that help?

Thank you very much, that's what I was looking for. I'm newbie in postgres programming. I needed this function for Postfix, which complains to the maillog when the SQL query returns 1 row with zero-lenght string instead of 0 rows.

Best regards, Tomas