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
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
