主页 / dba / 问题 / 55837
Accepted
A-K
Asked: 2013-12-31 14:43:02 +0800 CST

此查询是否始终正确检索数组参数的类型

  • 772

以下查询似乎有效，但我不确定它是否是最佳/始终正确的方法：

select 
parameter_name, 
CASE WHEN data_type='ARRAY' 
    THEN SUBSTRING(udt_name, 2)||'[]' 
    ELSE data_type 
END AS data_type
from information_schema.parameters 
where specific_schema='my_schema'
and parameter_mode='IN'
and specific_name like 'my_proc_16831'
order by ordinal_position

无论如何，它对我来说看起来有点老套。例如，如果我的参数是数字数组，是否有更好的方法来检索 numeric[] 作为类型？

postgresql information-schema

1 个回答

  1. Best Answer

    我想你想要format_type(typid oid, typmod integer)。

    鉴于：

    CREATE SCHEMA my_schema;

CREATE FUNCTION my_schema.my_proc_16831(numeric[])
RETURNS numeric[] LANGUAGE sql AS $$
SELECT $1;
$$;

    您可以通过以下方式获取过程详细信息：

    SELECT
  p.parameter_name,
  format_type((p.udt_schema||'.'||p.udt_name)::regtype, -1) 
FROM information_schema.parameters AS p
INNER JOIN information_schema.routines 
  USING (specific_catalog, specific_schema, specific_name) 
WHERE specific_schema = 'my_schema'   
  AND parameter_mode = 'IN'
  AND routine_name = 'my_proc_16831';

    ...尽管这消除了 using 的可移植性information_schema，因为您依赖于 Pg 特定行为。它还假定 typmod 始终为 -1，因为 PostgreSQL 不跟踪函数的 typmod - 例如，numeric(18,2)成为numeric函数参数。

    至少这样你不需要使用你的LIKEhack，或者依赖于关于数组内部类型名称的可变假设。

    顺便说一句，您可以直接使用pg_procandpg_type代替。如果性能至关重要，那么这可能是值得做的，但否则information_schema将更具跨版本可移植性。

    这给了你等价物：

    SELECT 
  proargnames[argpos] AS arg_name, 
  format_type(proargtypes[argpos],-1) AS arg_type 
FROM pg_proc p 
INNER JOIN pg_namespace n ON (p.pronamespace = n.oid) 
CROSS JOIN LATERAL generate_subscripts(proargtypes, 1) argpos
WHERE n.nspname = 'my_schema'
  AND p.proname = 'my_proc_16831' 
/* AK: suggested additional clause
      AND argpos > 0
*/
    ORDER BY p.proname, argpos;
    • 4

相关问题