Postgres pg_catalog常量查找策略

Question

短版本：--我花了一些时间在pg_catalog上，希望在使用char代码的地方显示扩展的定义。例如，对于pg_class.relkind来说，“复合类型”而不是"c“。我尝试过自定义函数和查找汤桌。我希望能得到一些建议，很可能，我忽略了一些显而易见的事情。

Postgres 11.5，在RDS上的部署(没有超级用户)

更长版本：，我正在为我们的项目编写一些客户端代码生成器和报告屏幕，这意味着我需要深入研究pg_type、pg_class、pg_attribute等等。由于我认为是历史原因，pg_catalog are....opaque中的许多表名和列名。许多字段包括需要查找或记忆的字符代码。例如，pg_class.relkind持有一个值I，S，c，f，i，m，p，r，t或v.Eh？我可以把这些意思记下来，然后在我的脑子里翻译，但这是电脑能更容易做到的事情。所以，我想我应该写一个函数：

CREATE OR REPLACE FUNCTION data.relkind_name (relkind text, out relkind_name text)
  RETURNS text
 AS $$
SELECT CASE
  WHEN relkind = 'r' THEN 'table'
  WHEN relkind = 'i' THEN 'index'
  WHEN relkind = 'S' THEN 'sequence'
  WHEN relkind = 't' THEN 'TOAST table'
  WHEN relkind = 'v' THEN 'view'
  WHEN relkind = 'm' THEN 'materialized view'
  WHEN relkind = 'c' THEN 'composite type'
  WHEN relkind = 'f' THEN 'foreign table'
  WHEN relkind = 'p' THEN 'partitioned table'
  WHEN relkind = 'I' THEN 'partitioned index'
  ELSE 'Unexpected relkind ' || relkind
END;
$$ LANGUAGE sql;

ALTER FUNCTION data.relkind_name (relkind text, out relkind_name text) OWNER TO user_bender;

那.好吧。它可以工作，但我不喜欢这样的函数，原因有两个: 1)数据被放入代码中，而不是数据结构。因此，您不能以任何方式重用/显示/验证它。2)对于每个常量类型，我都需要一个自定义函数。这让我想到了下一个想法，一个查找汤桌。

BEGIN;
DROP TABLE IF EXISTS data.constant CASCADE;

CREATE TABLE IF NOT EXISTS data.constant (
    theme text NOT NULL DEFAULT NULL,
    code text NOT NULL DEFAULT NULL, 
    label text NOT NULL DEFAULT NULL,

    PRIMARY KEY (theme, code)
);

ALTER TABLE data.constant OWNER TO user_change_structure;
COMMIT;

在进一步研究之前，我将规定抓取-所有查找表通常都值得嘲笑。这不是我会对动态的，用户驱动的数据做的事情。因为很糟糕。太糟糕了。但在这个狭窄的案例中，这似乎是一个坚实的想法：

• 数据被放入Postgres中，如果是这样的话，只有在主要版本的情况下才会进行更改。
• 所有这些数据都不会消失，或者至少不太可能。
• 一旦遇到令人感兴趣的事情，添加一组新的常量是非常容易的。

下面是pg_catalog中几个常量列表的设置：

INSERT INTO constant
    (theme,code,label)

VALUES
    ('typcategory','A','Array types'),
    ('typcategory','B','Boolean types'),
    ('typcategory','C','Composite types'),
    ('typcategory','D','Date/time types'),
    ('typcategory','E','Enum types'),
    ('typcategory','G','Geometric types'),
    ('typcategory','I','Network address types'),
    ('typcategory','N','Numeric types'),
    ('typcategory','P','Pseudo-types'),
    ('typcategory','R','Range types'),
    ('typcategory','S','String types'),
    ('typcategory','T','Timespan types'),
    ('typcategory','U','User-defined types'),
    ('typcategory','V','Bit-string types'),
    ('typcategory','X','unknown type'),
    ('relkind','r','ordinary table'),
    ('relkind','i','index'),
    ('relkind','S','sequence'),
    ('relkind','t','TOAST table'),
    ('relkind','v','view'),
    ('relkind','m','materialized view'),
    ('relkind','c','composite type'),
    ('relkind','f','foreign table'),
    ('relkind','p','partitioned table'),
    ('relkind','I','partitioned index');

因为数据在表中，所以您可以以正常的方式做正常的事情。或者甚至使用Postgres的出色的string_agg函数：

  select theme,
         string_agg(code, ', ' order by code) as constants 
    from constant 
group by theme
order by theme;

relkind I, S, c, f, i, m, p, r, t, v
typcategory A, B, C, D, E, G, I, N, P, R, S, T, U, V, X

或者一个简单的查询来进行查找：

-- I want a default/error result label if there is no match.
select coalesce((select label from constant where theme = 'relkind' and code  = 'X'),
                'Undefined')

它可以被包装成一个函数：

DROP FUNCTION IF EXISTS data.lookup (theme text, code text);

CREATE OR REPLACE FUNCTION data.lookup (theme text, code text)
    RETURNS TEXT 
AS $$
-- I want a default/error result label if there is no match, hence the subquery.
select coalesce(
                (select label 
                   from constant 
                  where theme = $1 and
                        code  = $2),
                'Undefined')
$$ LANGUAGE sql;

ALTER FUNCTION data.lookup (theme text, code text)  OWNER TO user_bender;

最后，对提供人类可读的结果的目录表进行查询：

select relowner::regrole,
        relnamespace::regnamespace,
        relname,
        lookup('relkind',relkind) as relkind_name,
        reltype::regtype

  from pg_class

您将从上面看到，我找到了一些类魔法铸造工具，以及一些系统信息功能。我希望使用lookup()函数来填补一些空白。

我很感谢你的评论和建议，即使这等于“把所有这些都扔出去，还有更好的办法。”

为了记录在案，我检查了自定义类型，魔术：：铸件，创建域(不适用)，ENUM (不适用，也不上诉)。我目前排除了构建一堆自定义视图的可能性，因为这会使我的代码更难为下一个人重新工作。(一个查找函数似乎不太需要学习。)

Answer 1

你可以用你想做的方式去做，如果你的味蕾对一个查找表的反应更好，那就去做吧。

我得到的印象是，你的动机在一定程度上是开玩笑的，因为经过一些曝光之后，你将能够轻松地记住频繁的一个字母密码。

在这种情况下，我建议多玩几个类型：

您可以创建一个自定义类型，其内部表示形式类似于"char"，但是类型输出函数会产生长的描述。类型输入函数将同时理解单个字符串和长名称。

对于relkind和其他短代码，会有这样的类型。

然后在IMPLICIT和新类型之间创建WITHOUT FUNCTION转换。如果需要，还可以创建(EXPLICIT)与text之间的转换。

整个过程将非常类似于regclass、regtype和相关的便利类型。

如果没有别的，这是对黑客PostgreSQL的一个很好的介绍。