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
