当前位置：首页 > news >正文

Postgresql数据库中通过函数实现将不确定列的数据插入到表中

news 来源：原创 2024/9/21 16:35:48

在数据库操作中，经常会遇到需要将不确定列的数据插入到表中的情况。这个文章将详细介绍如何在 PostgreSQL 中通过函数来实现这一需求，记录一下。

一、需求概述

在实际应用中，可能会接收到一个包含数据的 JSON 字符串，需要将其插入到指定表中。同时，要处理表的主键问题，确保在数据中没有主键值时能自动生成并添加。此外，还需要考虑表可能位于不同的模式（schema）下，以及后续的数据更新操作。

二、实现分析

为了实现上述需求，需要利用 PostgreSQL 丰富的函数和操作符来处理 JSON 数据、获取表结构信息、生成主键值以及构建和执行 SQL 语句。

三、实现步骤

第一步：获取表的所有字段

使用 information_schema.columns 表获取指定表和模式下的所有列名，并存储在 all_column_names 数组中。

第二步：查找主键字段及数据类型

通过查询 information_schema.key_column_usage 和相关表来确定主键列名和数据类型。

第三步：如果 Json 没有主键字段，则给 Json 增加主键字段

将输入的 JSON 字符串转换为 JSONb 类型，并检查主键列在数据中是否存在。如果不存在且主键为整数类型，从表中获取最大值并加 1 作为主键值；如果主键为其他类型，则生成一个随机 UUID 作为主键值。之后创建包含主键的新 JSON 数据并与原数据合并。

第四步：构建插入语句的列和值

接下来，构建插入语句的列和值部分。遍历 all_column_names 数组，只添加数据中存在值的列到插入语句中。

第五步：插入

最后，执行构建好的插入语句，将数据插入到表中。

四、测试

为了验证函数的正确性，进行如下测试操作：

CREATE TABLE test_table (id VARCHAR(50) PRIMARY KEY,name VARCHAR(50),age INT
);CREATE TABLE test_table2 (id int PRIMARY KEY,name VARCHAR(50),age INT
);SELECT insert_data_to_table('{"name": "Alice", "age": 25,"id": "55655655" }', 'test_table');
SELECT insert_json_data('{"name": "Aliceb", "age": 26}', 'test_table');
SELECT insert_json_data('{"name": "Alicec", "age": 27}', 'test_table');
SELECT insert_json_data('{}', 'test_table2');

插入后的效果

五、结果

经过测试，上述函数能够成功地将不确定列的数据插入到表中，并根据给定的条件进行更新操作。在实际应用中，我们可以根据具体的业务需求灵活调整函数的参数和逻辑，以满足更多复杂的场景。

六、附：完整的函数

-- 插入数据函数
CREATE OR REPLACE FUNCTION insert_data_to_table(json_string TEXT, tName TEXT, SchemaName TEXT DEFAULT 'public')
RETURNS VOID AS $$
DECLAREall_column_names TEXT[];primary_key_column_name TEXT;primary_key_data_type TEXT;json_data JSONb;new_json_data jsonb;insert_statement TEXT;key_value TEXT;max_value INT;  -- 用于存储主键的最大值
BEGIN-- 第一步：获取表的所有字段SELECT array_agg(column_name) INTO all_column_namesFROM information_schema.columnsWHERE table_name = tName AND table_schema = SchemaName;-- 第二步：查找主键字段及数据类型SELECT kcu.column_name, data_type INTO primary_key_column_name, primary_key_data_typeFROM information_schema.key_column_usage kcuJOIN information_schema.columns col ON kcu.column_name = col.column_nameWHERE kcu.table_name = tName AND col.table_schema = SchemaName AND kcu.constraint_name IN (SELECT constraint_name FROM information_schema.table_constraints WHERE table_name = tName AND table_schema = SchemaName AND constraint_type = 'PRIMARY KEY');-- 第三步：如果 Json 没有主键字段，则给 Json 增加主键字段json_data := json_string::JSONb;IF primary_key_column_name IS NOT NULL AND json_data ->> primary_key_column_name IS NULL THENIF primary_key_data_type = 'integer' THEN-- 获取主键的最大值EXECUTE 'SELECT coalesce(max(' || primary_key_column_name || '), 0) + 1 FROM ' || SchemaName || '.' || tName INTO max_value;key_value := max_value;  -- 使用获取到的最大值加 1 作为主键值ELSEkey_value := gen_random_uuid()::TEXT;END IF;new_json_data := concat('{', '"', primary_key_column_name, '": "', key_value, '"}')::jsonb;json_data := (new_json_data || json_data);END IF;-- 第四步：构建插入语句的列和值insert_statement := 'INSERT INTO ' || SchemaName || '.' || tName || ' (';FOR i IN 1..array_length(all_column_names, 1) LOOPIF json_data ->> all_column_names[i] IS NOT NULL THENinsert_statement := insert_statement || all_column_names[i] || ', ';END IF;END LOOP;insert_statement := substring(insert_statement, 1, length(insert_statement) - 2) || ') VALUES (';FOR i IN 1..array_length(all_column_names, 1) LOOPIF json_data ->> all_column_names[i] IS NOT NULL THENinsert_statement := insert_statement || quote_literal(json_data ->> all_column_names[i]) || ', ';END IF;END LOOP;insert_statement := substring(insert_statement, 1, length(insert_statement) - 2) || ')';-- 第五步：插入RAISE NOTICE '%', insert_statement;EXECUTE insert_statement;
END;
$$ LANGUAGE plpgsql;