CentOS 安装 Postgresql 13
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;
版权声明:
本站所有文章除特别声明外,均采用 CC BY-NC-SA 4.0 许可协议。转载请注明来自
海边的曼切斯特!
喜欢就支持一下吧