具有重复参数的PostgreSQL函数

概述我在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 SE 我在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报告.

select   parameter_mode,parameter_namefrom information_schema.parameterswhere specific_schema = 'pg_catalog'and specific_name like 'pg_stat_get_activity%'order by ordinal_position

以上收益率(另见本SQLFiddle)：

+--------------+----------------+|parameter_mode|parameter_name  |+--------------+----------------+|IN            |pID             ||OUT           |datID           ||OUT           |pID             ||...           |...             |+--------------+----------------+

天真地,我尝试创建一个类似的功能,但没有用：

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函数和我的函数有什么不同？为什么我不能使用这种语法？

我知道这些是相当学术性的问题,但我需要正确理解这一点,以便在jOOQ代码生成器中修复issue.

我注意到它出现在9.2中.在9.1版中,out字段名为procpID：

 parameter_mode |  parameter_name  ----------------+------------------ IN             | pID OUT            | datID OUT            | procpID OUT            | usesysID ...

寻找postgres git历史的变化导致这个提交：

commit 4f42b546fd87a80be30c53a0f2c897acb826ad52Author: 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

在改变的行中,这是感兴趣的行：

-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,25,16,1184,869,23}" "{i,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,pID,state,query,state_change,clIEnt_port}" _null_ pg_stat_get_activity _null_ _null_ _null_ )); 

在这种预先消化的形式中,作者没有注意到pID的双重使用,或者他们并不关心,因为它在实践中是无害的,这似乎是合理的.

它是通过的,因为这些内部函数是由initdb在跳过普通用户函数的创建检查的快速路径中创建的.

总结

以上是内存溢出为你收集整理的具有重复参数的PostgreSQL函数全部内容，希望文章能够帮你解决具有重复参数的PostgreSQL函数所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。