具有重复参数的PostgreSQL函数 [英] PostgreSQL function with duplicate parameters
问题描述
我偶然发现了pg_catalog.pg_stat_get_activity
中一个奇怪的函数签名:
I stumbled upon a curious function signature in pg_catalog.pg_stat_get_activity
:
CREATE OR REPLACE FUNCTION pg_stat_get_activity(
IN pid integer,
OUT datid oid,
OUT pid integer,
-- more parameters...)
RETURNS SETOF record AS 'pg_stat_get_activity'
LANGUAGE internal STABLE
COST 1
ROWS 100;
此函数两次声明相同的参数名称,这也从information_schema
中报告.
This function declares the same parameter name twice, which is also reported from the information_schema
.
select
parameter_mode,
parameter_name
from information_schema.parameters
where specific_schema = 'pg_catalog'
and specific_name like 'pg_stat_get_activity%'
order by ordinal_position
上面的结果(另请参见 SQLFiddle ):
The above yields (see also this SQLFiddle):
+--------------+----------------+
|parameter_mode|parameter_name |
+--------------+----------------+
|IN |pid |
|OUT |datid |
|OUT |pid |
|... |... |
+--------------+----------------+
天真的,我尝试创建类似的函数,但无济于事:
Naively, I tried creating a similar function, without avail:
CREATE FUNCTION f_2647(p1 IN int, p1 OUT int)
AS $$
BEGIN
p1 := p1;
END;
$$ LANGUAGE plpgsql;
我的问题:
- 为什么内部
pg_stat_get_activity
函数会两次重新声明相同的参数名称?目的是什么?例如.为什么不只使用INOUT
参数? - 内部
pg_stat_get_activity
功能和我的功能之间有什么区别?为什么我不能使用这种语法?
- Why does the internal
pg_stat_get_activity
function redeclare the same parameter name twice? What's the purpose of this? E.g. why not just use anINOUT
parameter? - What is different between the internal
pg_stat_get_activity
function and mine? Why can't I use this syntax?
我知道这些是学术性问题,但是我需要正确理解这一点才能解决 issue 在 jOOQ 代码生成器中.
I know these are rather academic questions, but I need to correctly understand this to fix an issue in the jOOQ codegenerator.
推荐答案
我注意到它出现在9.2版中.在9.1版中,out字段被命名为procpid
:
I notice that it appeared in 9.2. In version 9.1, the out field was named procpid
:
parameter_mode | parameter_name
----------------+------------------
IN | pid
OUT | datid
OUT | procpid
OUT | usesysid
...
寻找postgres git历史记录中的更改会导致此提交:
Looking for the change in postgres git history leads to this commit:
commit 4f42b546fd87a80be30c53a0f2c897acb826ad52
Author: Magnus Hagander
Date: Thu Jan 19 14:19:20 2012 +0100
Separate state from query string in pg_stat_activity
This separates the state (running/idle/idleintransaction etc) into
it's own field ("state"), and leaves the query field containing just
query text.
The query text will now mean "current query" when a query is running
and "last query" in other states. Accordingly,the field has been
renamed from current_query to query.
Since backwards compatibility was broken anyway to make that, the procpid
field has also been renamed to pid - along with the same field in
pg_stat_replication for consistency.
Scott Mead and Magnus Hagander, review work from Greg Smith
在已更改的行中,这是您感兴趣的行之一:
Among the lines that changed, here is the one of interest:
-DATA(insert OID = 2022 ( pg_stat_get_activity PGNSP PGUID 12 1 100 0 0 f f f f t s 1 0 2249 "23" "{23,26,23,26,25,25,16,1184,1184,1184,869,25,23}" "{i,o,o,o,o,o,o,o,o,o,o,o,o}" "{pid,datid,procpid,usesysid,application_name,current_query,waiting,xact_start,query_start,backend_start,client_addr,client_hostname,client_port}" _null_ pg_stat_get_activity _null_ _null_ _null_ ));
+DATA(insert OID = 2022 ( pg_stat_get_activity PGNSP PGUID 12 1 100 0 0 f f f f t s 1 0 2249 "23" "{23,26,23,26,25,25,25,16,1184,1184,1184,1184,869,25,23}" "{i,o,o,o,o,o,o,o,o,o,o,o,o,o,o}" "{pid,datid,pid,usesysid,application_name,state,query,waiting,xact_start,query_start,backend_start,state_change,client_addr,client_hostname,client_port}" _null_ pg_stat_get_activity _null_ _null_ _null_ ));
以这种预先消化的形式,作者可能没有注意到重复使用pid
的情况,或者他们不在乎,因为它在实践中是无害的.
In this pre-digested form, it's plausible that the authors didn't notice the double-use of pid
, or else they didn't care since it's harmless in practice.
之所以能够通过,是因为这些内部功能是由initdb
在快速路径中创建的,该路径跳过了对普通用户功能的创建检查.
It is let through because these internal functions are created by initdb
in a fast path that skips the create checks of normal user functions.
这篇关于具有重复参数的PostgreSQL函数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!