Обсуждение: how to return 0 rows in function

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?
Returning NULL does not help (return NULL string in 1 row). Not to return a
value leads to error output.

Many thanks for help

Tomas

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

Am Freitag, 18. Mai 2007 10:20 schrieb Tomas Macek:
> ----------------
> 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?

Your function is returning a scalar value, not rows.  If you want it to return
0 rows, the first change should be to make it return rows at all, by
declaring the return type to be, say, SETOF varchar.  Then you can return no
rows by doing something like SELECT '' WHERE false;.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

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