用crypt创建一个新的列

Question

我目前正在尝试创建一个基于同一表(tb_customer)的其他列的密码的新列。创建密码的源是cust_cif列。

我使用的脚本如下:

ALTER TABLE erp.tb_customer 
ADD COLUMN password CHAR (34) USING crypt(erp.tb_customer.cust_cif, gen_salt('md5'));

我得到一个错误，不知道为什么，因为md5不需要安装(但我还是这样做了)。

错误如下:

ERROR:  does not exist type «crypt»
LINE 2:   ADD COLUMN password crypt(erp.tb_customer.cust_cif, gen_sa...

我也试过

ALTER TABLE erp.tb_customer 
ADD COLUMN password SELECT crypt(erp.tb_customer.cust_cif, gen_salt('md5'));

下面是表tb_customer中显示的一些值:

cust_no | cust_name | cust_cif | last_updated_by | last_updated_date

"C0001" "PIENSOS MARTIN"    "A12345678" "SYSTEM"    "2022-04-28"
"C0002" "AGRICULTURA VIVES" "A66666666" "SYSTEM"    "2022-04-28"
"C0003" "CULTIVOS MARAVILLA"    "A55555555" "SYSTEM"    "2022-04-28"
"C0004" "ASOCIADOS PEREZ"   "A23126743" "SYSTEM"    "2022-04-28"
"C0005" "TECNICOS AVA"  "B34211233" "SYSTEM"    "2022-04-28"
"C0006" "AGR AGRI"  "B78788999" "SYSTEM"    "2022-04-28"
"C0007" "AGRIMARCOS"    "B98766562" "SYSTEM"    "2022-04-28"
"C0008" "CULTIVANDO ALEGRIA"    "B12333123" "SYSTEM"    "2022-04-28"
"C0009" "MARCOS LIMPIEZA"   "A87727711" "SYSTEM"    "2022-04-28"
"C0010" "VIAJES MUNDO"  "A00099982" "SYSTEM"    "2022-04-28"

我做错了什么?

提前谢谢你。

Answer 1

没有ADD COLUMN...USING

您必须在不同的语句中添加列并填充它(UPDATE)。可能在同一事务中。

Answer 2

我会这样做:

ALTER TABLE erp.tb_customer 
ADD COLUMN password_cryp char(100);
--CREATE EXTENSION pgcrypto;
CREATE OR REPLACE PROCEDURE erp.col_cryp() AS $$
DECLARE
_row record;
BEGIN
FOR _row IN (
SELECT cust_cif FROM erp.tb_customer
)
LOOP
UPDATE erp.tb_customer
SET password_cryp = crypt('cust_cif', gen_salt('md5'));
END LOOP;
END;
$$LANGUAGE plpgsql;
CALL erp.col_cryp();

你可以试试:

SELECT ("password_cryp " = 'copy here the generated code on the table') AS IsMatch FROM erp.tb_customer;

希望它能起作用!