Tips n’ Tricks – return nothing from PL/PgSQL function

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.

6 thoughts on “Tips n’ Tricks – return nothing from PL/PgSQL function”

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

  3. 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

  4. 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.

  5. you could also use

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

    instead of creating another function

Comments are closed.