postgresql - 获取在postgresql中返回记录的函数的复杂输出类型

Question

我想就我的 postgresql 数据库中的函数编写漂亮而详细的报告。我构建了以下查询:

SELECT routine_name, data_type, proargnames
FROM   information_schema.routines
join pg_catalog.pg_proc on pg_catalog.pg_proc.proname = information_schema.routines.routine_name
WHERE specific_schema = 'public'
ORDER  BY routine_name;

它按预期工作(基本上返回我想要的:函数名称、输出数据类型和输入数据类型)除了一件事:我有比较复杂的功能，其中很多返回record。问题是，对于此类函数，data_type 也会返回给我 record，而我想要函数输出类型的详细列表。例如，我的一个函数中有这样的东西:

RETURNS TABLE("Res" integer, "Output" character varying) AS

我怎样才能在上面进行查询(或者，也许，一个新的查询，如果它能解决问题)返回类似integer, character varying 而不是 record 这样的函数？
我正在使用 postgresql 9.2
提前致谢!

Answer 1

RECORD 返回值在运行时计算，无法通过这种方式检索信息。

但是，如果使用 RETURNS TABLE("Res"integer, "Output"character varying) AS，则有一个解决方案。

我使用的测试函数:

-- first function, uses RETURNS TABLE
CREATE FUNCTION test_ret(a TEXT, b TEXT) 
  RETURNS TABLE("Res" integer, "Output" character varying) AS $$
DECLARE                           
  ret RECORD;                                                                                   
BEGIN                           
  -- test
END;$$ LANGUAGE plpgsql;

-- second function, test some edge cases
-- same name as above, returns simple integer
CREATE FUNCTION test_ret(a TEXT)
  RETURNS INTEGER AS $$
DECLARE
  ret RECORD;
BEGIN
-- test
END;$$ LANGUAGE plpgsql;

如何检索此函数返回数据类型很容易，因为它存储在 pg_catalog.pg_proc.proallargtypes 中，问题是这是一个 OID 数组。我们必须取消嵌套这个东西并将其加入 pg_catalog.pg_types.oid。

-- edit: add support for function not returning tables, thx Tommaso Di Bucchianico
WITH pg_proc_with_unnested_proallargtypes AS (
   SELECT
      pg_catalog.pg_proc.oid,
      pg_catalog.pg_proc.proname,
      CASE WHEN proallargtypes IS NOT NULL THEN unnest(proallargtypes) ELSE null END AS proallargtype
    FROM pg_catalog.pg_proc
      JOIN pg_catalog.pg_namespace ON pg_catalog.pg_proc.pronamespace = pg_catalog.pg_namespace.oid
    WHERE pg_catalog.pg_namespace.nspname = 'public'
),
  pg_proc_with_proallargtypes_names AS (
    SELECT
      pg_proc_with_unnested_proallargtypes.oid,
      pg_proc_with_unnested_proallargtypes.proname,
      array_agg(pg_catalog.pg_type.typname) AS proallargtypes
    FROM pg_proc_with_unnested_proallargtypes
      LEFT JOIN pg_catalog.pg_type ON pg_catalog.pg_type.oid = proallargtype
    GROUP BY
      pg_proc_with_unnested_proallargtypes.oid,
      pg_proc_with_unnested_proallargtypes.proname
  )
SELECT
  information_schema.routines.specific_name,
  information_schema.routines.routine_name,
  information_schema.routines.routine_schema,
  information_schema.routines.data_type,
  pg_proc_with_proallargtypes_names.proallargtypes
FROM information_schema.routines
    -- we can declare many function with the same name and schema as long as arg types are different
    -- This is the only right way to join pg_catalog.pg_proc and information_schema.routines, sadly
 JOIN pg_proc_with_proallargtypes_names 
    ON pg_proc_with_proallargtypes_names.proname || '_' ||  pg_proc_with_proallargtypes_names.oid = information_schema.routines.specific_name
;

欢迎任何重构:)

结果如下:

 specific_name  | routine_name | routine_schema | data_type |      proallargtypes      
----------------+--------------+----------------+-----------+--------------------------
 test_ret_16633 | test_ret     | public         | record    | {text,text,int4,varchar}
 test_ret_16635 | test_ret     | public         | integer   | {NULL}
(2 rows)

---

编辑输入和输出参数的识别并不简单，这是我针对 pg 9.2 的解决方案

-- https://gist.github.com/subssn21/e9e121f6fd5ff50f688d
-- Allow us to use array_remove in pg < 9.3
CREATE OR REPLACE FUNCTION array_remove(a ANYARRAY, e ANYELEMENT)
  RETURNS ANYARRAY AS $$
BEGIN
  RETURN array(SELECT x FROM unnest(a) x WHERE x <> e);
END;
$$ LANGUAGE plpgsql;

-- edit: add support for function not returning tables, thx Tommaso Di Bucchianico
WITH pg_proc_with_unnested_proallargtypes AS (
    SELECT
      pg_catalog.pg_proc.oid,
      pg_catalog.pg_proc.proname,
      pg_catalog.pg_proc.proargmodes,
      CASE WHEN proallargtypes IS NOT NULL THEN unnest(proallargtypes) ELSE null END AS proallargtype
    FROM pg_catalog.pg_proc
      JOIN pg_catalog.pg_namespace ON pg_catalog.pg_proc.pronamespace = pg_catalog.pg_namespace.oid
    WHERE pg_catalog.pg_namespace.nspname = 'public'
),
pg_proc_with_unnested_proallargtypes_names_and_mode AS (
    SELECT
      pg_proc_with_unnested_proallargtypes.oid,
      pg_proc_with_unnested_proallargtypes.proname,
      pg_catalog.pg_type.typname,
      -- we can't unnest multiple array of same length the way we expect in pg 9.2
      -- just retrieve each mode manually using type row_number
      pg_proc_with_unnested_proallargtypes.proargmodes[row_number() OVER w] AS proargmode
    FROM pg_proc_with_unnested_proallargtypes
      LEFT JOIN pg_catalog.pg_type ON pg_catalog.pg_type.oid = proallargtype
    WINDOW w AS (PARTITION BY pg_proc_with_unnested_proallargtypes.proname)
),
pg_proc_with_input_and_output_type_names AS (
    SELECT
      pg_proc_with_unnested_proallargtypes_names_and_mode.oid,
      pg_proc_with_unnested_proallargtypes_names_and_mode.proname,
      array_agg(pg_proc_with_unnested_proallargtypes_names_and_mode.typname) AS proallargtypes,
      -- we should use FILTER, but that's not available in pg 9.2 :(
      array_remove(array_agg(
          -- see documentation for proargmodes here: http://www.postgresql.org/docs/9.2/static/catalog-pg-proc.html
          CASE WHEN pg_proc_with_unnested_proallargtypes_names_and_mode.proargmode = ANY(ARRAY['i', 'b', 'v'])
          THEN pg_proc_with_unnested_proallargtypes_names_and_mode.typname 
          ELSE NULL END
      ), NULL) AS proinputargtypes,
      array_remove(array_agg(
         -- see documentation for proargmodes here: http://www.postgresql.org/docs/9.2/static/catalog-pg-proc.html
         CASE WHEN pg_proc_with_unnested_proallargtypes_names_and_mode.proargmode = ANY(ARRAY['o', 'b', 't'])
         THEN pg_proc_with_unnested_proallargtypes_names_and_mode.typname
         ELSE NULL END
      ), NULL) AS prooutputargtypes
    FROM pg_proc_with_unnested_proallargtypes_names_and_mode
    GROUP BY
      pg_proc_with_unnested_proallargtypes_names_and_mode.oid,
      pg_proc_with_unnested_proallargtypes_names_and_mode.proname
)
SELECT 
  *
FROM pg_proc_with_input_and_output_type_names
;

这是我的示例输出:

  oid  |   proname    |      proallargtypes      | proinputargtypes | prooutputargtypes 
-------+--------------+--------------------------+------------------+-------------------
 16633 | test_ret     | {text,text,int4,varchar} | {text,text}      | {int4,varchar}
 16634 | array_remove | {NULL}                   | {}               | {}
 16635 | test_ret     | {NULL}                   | {}               | {}
(3 rows)

希望对您有所帮助:)