18

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?

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • 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' Dec 04 '11 at 00: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 Dec 05 '11 at 08:22
  • And several years late [I stumble](http://dba.stackexchange.com/q/65310/1396) 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). –  May 17 '14 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 May 17 '14 at 21:16

1 Answers1

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.

Mike Sherrill 'Cat Recall'
  • 91,602
  • 17
  • 122
  • 185