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
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
