PostgreSQL functions returning void

Question

Functions written in PL/pgSQL or SQL can be defined as RETURNS void. I recently stumbled upon an odd difference in the result.

Consider the following demo:

CREATE OR REPLACE FUNCTION f_sql()
  RETURNS void
  LANGUAGE sql AS
'SELECT NULL::void';  -- = "do nothing", no special meaning


CREATE OR REPLACE FUNCTION f_plpgsql()
  RETURNS void
  LANGUAGE plpgsql AS
$func$
BEGIN
NULL;  -- = "do nothing", no special meaning
END;
$func$;

_{The function f_sql() uses the only possible way for a SELECT (as last command) in a SQL function that RETURNS void. I use it just because it is the simplest way for the purposes of this test - any other function, with UPDATE or DELETE for instance, shows the same behavior.}

Now, void is a fictive type. While the plpgsql function seems to return the equivalent of an empty string as type void - effectively ''::void. The sql function seems to return NULL::void.

db=# SELECT f_sql() IS NULL;
 ?column?
----------
 t

db=# SELECT f_sql()::text IS NULL;
 ?column?
----------
 t

db=# SELECT f_plpgsql() IS NULL;
 ?column?
----------
 f

db=# SELECT f_plpgsql()::text = '';
 ?column?
----------
 t

This can have subtle and confusing side effects.
What is the reason behind the difference?

They're declared to return void; maybe we have no business looking at how their returned values compare to anything. (If you really wanted to discourage people from looking at the return value, you could make it return a random value. I might do that next time I write something that should return void.) — Mike Sherrill 'Cat Recall'
– Mike Sherrill 'Cat Recall', Commented Dec 4, 2011 at 0:57
@Catcall: Yeah, one might argue that it was an error to check a void value at all. Still it feels buggy that the value is different depending on the chosen language. That should not be. I'll file a bug report when I get around to do it and we'll see what the core team thinks of that. — Erwin Brandstetter
– Erwin Brandstetter, Commented Dec 5, 2011 at 8:22
And several years late I stumble on the same thing! Still I've learned your useful trick of SELECT NULL::void in a void returning sql function: btw the chapter seems to be gone from the link you gave (though the trick still works in 9.3). — user533832
– user533832, Commented May 17, 2014 at 20:52
@JackDouglas. Thanks for the update. Pavel obviously removed the chapter from the site since we discussed it here. I have removed the dead link now. — Erwin Brandstetter
– Erwin Brandstetter, Commented May 17, 2014 at 21:16

Mike Sherrill 'Cat Recall' · Accepted Answer · 2011-11-30 03:40:32Z

11

(I'm no expert in this source code. You've been warned.)

The source is online here. I've omitted the filenames; you can search for the function names to find their definitions. I left the line numbers (usually) because it's easier to cut and paste, and different line numbers will mean the source has changed.

The short story is that some "void" returns are probably empty cstrings (empty null-terminated strings), and others are null pointers.

Here are the parts of the source that look relevant.

00228 /*
00229  * void_out     - output routine for pseudo-type VOID.
00230  *
00231  * We allow this so that "SELECT function_returning_void(...)" works.
00232  */
00233 Datum
00234 void_out(PG_FUNCTION_ARGS)
00235 {
00236     PG_RETURN_CSTRING(pstrdup(""));
00237 }

00251 /*
00252  * void_send    - binary output routine for pseudo-type VOID.
00253  *
00254  * We allow this so that "SELECT function_returning_void(...)" works
00255  * even when binary output is requested.
00256  */
00257 Datum
00258 void_send(PG_FUNCTION_ARGS)
00259 {
00260     StringInfoData buf;
00261 
00262     /* send an empty string */
00263     pq_begintypsend(&buf);
00264     PG_RETURN_BYTEA_P(pq_endtypsend(&buf));
00265 }

We also have

00285 /* To return a NULL do this: */
00286 #define PG_RETURN_NULL()  \
00287     do { fcinfo->isnull = true; return (Datum) 0; } while (0)
00288 
00289 /* A few internal functions return void (which is not the same as NULL!) */
00290 #define PG_RETURN_VOID()     return (Datum) 0

So it makes sense to me that a user-defined function that returns through PG_RETURN_VOID() would not test equivalent to one that returns through void_out() or void_send(). I don't yet know why that is, but I have to stop and get some sleep.

answered Nov 30, 2011 at 3:40

Mike Sherrill 'Cat Recall'

96.6k20 gold badges133 silver badges195 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Erwin Brandstetter Over a year ago

Very interesting! Can this behavior be intentional? Maybe a bug?

Pavel Stehule Over a year ago

it is bug - please, report it

Pavel Stehule Over a year ago

but I am not sure what is fix - there is not consistent behave and test "void is null" has not sense too

Collectives™ on Stack Overflow

PostgreSQL functions returning void

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related