Re: how to return 0 rows in function

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?

--
   Richard Huxton
   Archonet Ltd