Re: Postgres Function in plpgsql, returning null results to Postfix

On Tuesday 24 May 2005 9:04 am, Simon Waters wrote:
> What is the correct syntax for returning a null or empty result?
>
> i.e. "RETURN NULL;" creates a one row, one column, result by the
> looks of it, which Postfix doesn't like.
>
> I'm currently doing the virtual_alias_map but I assume the answer
> is generic?
>
> Otherwise plpgsql functions seem a very easy and natural way to
> handle our more complex features.

This is a PostgreSQL question, not a Postfix question. "select null"
will return a single row, single column result with a null value. So
does "select some_function_which_returns_null()".

I don't know what problem you are trying to solve, but it sounds like
you need to read up on set returning functions in PostgreSQL.

The following snippet is from
http://www.varlena.com/varlena/GeneralBits/26.html and shows how you
can create a function that returns 0 or more rows of records.

CREATE OR REPLACE FUNCTION getemployeedid ( integer )
RETURNS SETOF integer as '
DECLARE
myrow RECORD;
retval integer;
BEGIN
FOR myrow IN SELECT * FROM employee where salary >= $1 LOOP
RETURN NEXT myrow.departmentid;
END LOOP;
RETURN;
END;
' language 'plpgsql';

Warning: set returning functions are find for small recordsets but
scale poorly for large return values as the full return set must be
assembled in memory before the function returns.

Cheers,
Steve