postgresql - 使用 sha-256 散列 postgres 中的列

Question

我有一个 postrges 数据库，其中的一个表包含我使用 python 函数生成的关键代码。我希望能够对该列进行哈希处理，以便每次向其添加 key 代码时，对 key 进行哈希处理。我怎样才能让 postgres 做到这一点？或者什么是存储这些代码的最佳方式。这是我想在我的表中散列的列示例。

 key_codes | 
-----------+
 L7G4J83K  |        
 J70KG169  |         
 L69E540K  |        
 GL8E9C3J  |         
 6C0LE215  |         
 9G01C8JA  |         
 1G9KC58A  |         

Answer 1

使用触发器在插入和更新时设置哈希列。对于 SHA-256，use the pgcrypto extension module's digest function .

由于您没有指定您的 PostgreSQL 版本，我假设您在以下示例中使用当前的 9.2。

调用 sha256 摘要函数的方法如下:

regress=# CREATE EXTENSION pgcrypto;
CREATE EXTENSION

regress=> SELECT digest('blah', 'sha256');
                               digest                               
--------------------------------------------------------------------
 \x8b7df143d91c716ecfa5fc1730022f6b421b05cedee8fd52b1fc65a96030ad52
(1 row)

请注意，CREATE EXTENSION 函数必须以 super 用户身份运行。

触发器非常简单。假设您的表格如下所示，这样的事情就可以了:

CREATE TABLE some_table ( key_codes text, hash bytea );

CREATE OR REPLACE FUNCTION hash_update_tg() RETURNS trigger AS $$
BEGIN
    IF tg_op = 'INSERT' OR tg_op = 'UPDATE' THEN
        NEW.hash = digest(NEW.key_codes, 'sha256');
        RETURN NEW;
    END IF;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER some_table_hash_update 
BEFORE INSERT OR UPDATE ON some_table 
FOR EACH ROW EXECUTE PROCEDURE hash_update_tg();

用法:

regress=> INSERT INTO some_table(key_codes) VALUES ('fred');
INSERT 0 1
regress=> SELECT * FROM some_table;
 key_codes |                                hash                                
-----------+--------------------------------------------------------------------
 fred      | \xd0cfc2e5319b82cdc71a33873e826c93d7ee11363f8ac91c4fa3a2cfcd2286e5
(1 row)

您可以通过使更新触发器有条件地减少触发器执行的开销。代替上面的 CREATE TRIGGER，使用这两个:

CREATE TRIGGER some_table_hash_insert
BEFORE INSERT ON some_table 
FOR EACH ROW 
EXECUTE PROCEDURE hash_update_tg(); 

CREATE TRIGGER some_table_hash_update 
BEFORE UPDATE ON some_table 
FOR EACH ROW 
WHEN ( NEW.key_codes IS DISTINCT FROM OLD.key_codes ) 
EXECUTE PROCEDURE hash_update_tg();