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.