May 3rd, 2011 by depesz | Tags: , , , , , | 6 comments »
Did it help? If yes - maybe you can help me?

Every now and then I need a function that returns nothing.

As you perhaps know, even defining function as “RETURNS VOID" doesn't fully solve the problem:

$
BEGIN;
CREATE function testit() RETURNS void as $$
BEGIN
RETURN;
END;
$$ language plpgsql;
CREATE FUNCTION
 
$ SELECT testit();
 testit
--------
 
(1 row)

1 row?! Clearly there is something returned.

So, can it be done any better? Well, yes. Since (as we saw) the function returns at least one row, perhaps we can make it to return 0-row recordset? Like this:

$ CREATE function testit() RETURNS setof void as $$
BEGIN
RETURN;
END;
$$ language plpgsql;
CREATE FUNCTION
 
$ SELECT testit();
 testit
--------
(0 rows)

When using older version of PostgreSQL, you might get error like:

$ select testit();
ERROR:  set-valued function called in context that cannot accept a set
CONTEXT:  PL/pgSQL function "testit" while casting return value to function's return type

In such case, just change the query to:

$ select * from testit();
 testit
--------
(0 rows)

One last thing – if you're running it via psql, and you want to get rid of the “testit" header, line below, and “0 rows" summary, just run psql with “-t" option, or enter “\t" in psql session, to turn on “tuples-only" mode.

  1. 6 comments

  2. May 3, 2011

    SELECT scalarfunction() should to return one row everywhere – what is strange?

  3. May 3, 2011

    because when I define something as “void” i expect the result to be .. well empty. 1 row is not empty.

  4. May 4, 2011

    It is strange – but void is scalar type too – “void” is just workaround for emulation procedures, but it isn’t real solution. And you can do

    SELECT voidfunc(), novoidfunc()

    Pavel

  5. May 4, 2011

    I know. Just so that it will be clear – I’m not bashing PostgreSQL – just providing way to work around something which did bother me, and I saw that someone else had the problem too.

  6. # Anonymous
    May 5, 2011

    you could also use

    select * from (select testit() as foo) as bar where foo isnull;

    instead of creating another function

  7. # Anonymous
    Oct 17, 2014

    nice

Leave a comment