sql - Postgresql:如何检查过程的哪些参数具有默认值？

Question

我在 Postgresql 中定义了一个存储过程:

create or replace function 
  test1(arg1 text, opt1 text default 'default value') 
  returns text as $$ select $1 || ' ' || $2; $$ language sql;

我想检查这个函数定义以找出答案:

1. 哪些参数有默认值
2. (奖励)默认值是什么

我尝试比较示例的 information_schema.parameters 记录:

select * from information_schema.parameters;

(使用diff -y --suppress-common-lines)

-[ RECORD 4808 ]---------+----------------------- | -[ RECORD 4809 ]---------+-----------------------------------
ordinal_position         | 1                      | ordinal_position         | 2
parameter_name           | arg1                   | parameter_name           | opt1
dtd_identifier           | 1                      | dtd_identifier           | 2

我设法找到默认值引用的唯一地方是在 pg_proc 表中:

select proargdefaults from pg_proc where proname = 'test1' ;

-[ RECORD 1 ]--+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
proargdefaults | ({CONST :consttype 25 :consttypmod -1 :constcollid 100 :constlen -1 :constbyval false :constisnull false :location 62 :constvalue 10 [ 40 0 0 0 119 105 98 98 108 101 ]})

末尾方括号中的那一位似乎包含某种内部格式的默认值:

(使用 Python)

>>> d="68 0 0 0 100 101 102 97 117 108 116 32 118 97 108 117 101"
>>> "".join([chr(int(a)) for a in d.split()])
'D\x00\x00\x00default value'

我的问题:是否有更好的方法来检查此函数以了解默认参数？

更新我正在寻找能够产生类似输出的东西

-[ RECORD 1 ]----+----------------------------
arg_index        | 1
arg_name         | arg1
arg_has_default  | f
arg_default_text | <null>
-[ RECORD 2 ]----+----------------------------
arg_index        | 2
arg_name         | opt1
arg_has_default  | t
arg_default_text | default value

Answer 1

您可以从 pg_proc 中选择可选参数，但您可能无法在 PostgreSQL 端解析它们的默认值表达式(因为它们可以有不同的类型)。

select proargnames[pronargs-pronargdefaults+1:pronargs] optargnames,
       pg_get_expr(proargdefaults, 0) optargdefaults
from   pg_proc
where  proname = 'test1'

SQLFiddle

编辑:找到了一种轻松将默认值解析为 json 值的方法(键是参数名称，值是默认值的 json 表示形式):

create or replace function proargdefaultsjson(proc pg_proc) returns json
language plpgsql
as $function$
declare
  expr_parsed record;
begin
  execute format(
    'select * from (values (%s)) v(%s)',
    pg_get_expr(proc.proargdefaults, 0),
    array_to_string(array(
      select quote_ident(n)
      from unnest(proc.proargnames[proc.pronargs-proc.pronargdefaults+1:proc.pronargs]) n
    ), ',')
  ) into expr_parsed;
  return row_to_json(expr_parsed);
end
$function$;

此函数应该在 PostgreSQL 9.2+ 中工作:SQLFiddle

编辑 2:您可以使用 hstore module 实现类似的效果，如果您使用 hstore(expr_parsed); 返回(这种情况下您将得到每个默认表达式的文本表示形式)。