CentOS 安装 Postgresql 13

1.安装

sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
sudo yum install -y postgresql13-server
sudo /usr/pgsql-13/bin/postgresql-13-setup initdb
sudo systemctl enable postgresql-13
sudo systemctl start postgresql-13

2.配置

vi /var/lib/pgsql/13/data/postgresql.conf 
# 取消 listen_addresses 的注释，将值改为 “*”。

vi /var/lib/pgsql/14/data/pg_hba.conf
# IPv4 local connections: 增加第二行
host    all             all             127.0.0.1/32            scram-sha-256
host    all             all             0.0.0.0/0               md5

# 重启服务
systemctl restart postgresql-13.service

3.登录

su - postgres
psql -U postgres

4.创建用户 admin 赋予创建数据库权限

create user admin with password 'your password';
grant all privileges on database postgres to admin;
GRANT postgres TO admin;
ALTER ROLE admin CREATEROLE CREATEDB;

5. JSON 数组查询

[{
  "DATA_ELEMENT_NAME": "患者主索引号码",
  "DATA_ELEMENT_EN_NAME": "PATIENT_ID",
  "DATA_ELEMENT_VALUE": "11111111"
},
{
  "DATA_ELEMENT_NAME": "患者姓名",
  "DATA_ELEMENT_EN_NAME": "PATIENT_NAME",
  "DATA_ELEMENT_VALUE": "张三"
}]

比如有个JSON数组，我要取DATA_ELEMENT_EN_NAME为PATIENT_ID的DATA_ELEMENT_VALUE值11111111，就这样写，查询结果就是 11111111

select get_data_element_value_by_json('[{
  "DATA_ELEMENT_NAME": "患者主索引号码",
  "DATA_ELEMENT_EN_NAME": "PATIENT_ID",
  "DATA_ELEMENT_VALUE": "11111111"
},
{
  "DATA_ELEMENT_NAME": "患者姓名",
  "DATA_ELEMENT_EN_NAME": "PATIENT_NAME",
  "DATA_ELEMENT_VALUE": "张三"
}]', 'PATIENT_ID')

CREATE OR REPLACE FUNCTION get_data_element_value(
  p_msg_id text,
  p_data_element_id text
)
RETURNS text AS $$
DECLARE
  v_data_element_value text;
BEGIN
  SELECT jsonb_extract_path_text(elem, 'DATA_ELEMENT_VALUE') INTO v_data_element_value
  FROM caradigm_message,
       jsonb_array_elements(msg_content->'message'->'CRITICAL_VALUE') AS elem
  WHERE msg_id = p_msg_id AND
        elem @> jsonb_build_object('DATA_ELEMENT_ID', p_data_element_id);

  RETURN v_data_element_value;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION get_data_element_value_by_json(json_data JSONB, element_en_name TEXT)
RETURNS TEXT AS $$
DECLARE
    result TEXT;
BEGIN
    SELECT elem->>'DATA_ELEMENT_VALUE' INTO result
    FROM jsonb_array_elements(json_data) as elem
    WHERE elem->>'DATA_ELEMENT_EN_NAME' = element_en_name;

    RETURN result;
END;
$$ LANGUAGE plpgsql;