PostgreSQL函数返回void [英] PostgreSQL functions returning void

问题描述

用PL/pgSQL或SQL编写的功能可以定义为RETURNS void.我最近偶然发现结果有一个奇怪的差异.

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

请考虑以下演示:

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

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

_{函数f_sql()在RETURNS void的SQL函数中使用SELECT的唯一可能方式(作为最后一个命令).我之所以使用它，只是因为它是进行此测试的最简单方法-例如，其他任何功能，例如UPDATE或DELETE，都表现出相同的行为.}

_{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.}

现在，void是虚拟类型.虽然plpgsql函数似乎返回与void类型有效的空字符串等效的内容，实际上是''::void. sql函数似乎返回NULL::void.

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?

推荐答案

(我不是此源代码的专家.已经警告您.)

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

简短的故事是，某些无效"返回可能是空的cstring(空的以null终止的字符串)，而另一些则是null指针.

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 }

我们也有

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

所以对我来说有意义的是，通过PG_RETURN_VOID()返回的用户定义函数不会测试等效于通过void_out()或void_send()返回的函数.我还不知道为什么，但是我必须停下来睡一下.

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.

这篇关于PostgreSQL函数返回void的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！