pg json 测试

CREATE OR REPLACE FUNCTION InsertFromJSONBatch(
    p_json_data TEXT,
    p_batch_size INTEGER DEFAULT 1000
)
RETURNS INTEGER
LANGUAGE plpgsql
AS $$
DECLARE
    total_users INTEGER;
    total_products INTEGER;
    i INTEGER;
BEGIN
    -- 获取总记录数
    SELECT COUNT(*) INTO total_users 
    FROM json_array_elements(p_json_data::json->'users');
    
    SELECT COUNT(*) INTO total_products 
    FROM json_array_elements(p_json_data::json->'products');
    
    RAISE NOTICE '开始处理: 用户%, 产品%', total_users, total_products;
    
    -- 分批插入用户数据
    FOR i IN 0..((total_users - 1) / p_batch_size) LOOP
        INSERT INTO users (id, name, email, isActive)
        SELECT 
            (value->>'id')::INTEGER,
            value->>'name',
            value->>'email',
            (value->>'isActive')::BOOLEAN
        FROM (
            SELECT value, row_number() OVER () as rn
            FROM json_array_elements(p_json_data::json->'users')
        ) t
        WHERE rn BETWEEN (i * p_batch_size + 1) AND ((i + 1) * p_batch_size);
        
        IF i % 10 = 0 THEN
            RAISE NOTICE '用户数据进度: %/%', 
                LEAST((i + 1) * p_batch_size, total_users), total_users;
        END IF;
    END LOOP;
    
    -- 分批UPSERT产品数据
    FOR i IN 0..((total_products - 1) / p_batch_size) LOOP
        INSERT INTO products (productId, productName, price, category, created_at)
        SELECT 
            value->>'productId',
            value->>'productName',
            (value->>'price')::DECIMAL,
            value->>'category',
            CURRENT_TIMESTAMP
        FROM (
            SELECT value, row_number() OVER () as rn
            FROM json_array_elements(p_json_data::json->'products')
        ) t
        WHERE rn BETWEEN (i * p_batch_size + 1) AND ((i + 1) * p_batch_size)
        ON CONFLICT (productId) 
        DO UPDATE SET
            productName = EXCLUDED.productName,
            price = EXCLUDED.price,
            category = EXCLUDED.category,
            created_at = CURRENT_TIMESTAMP;
        
        IF i % 10 = 0 THEN
            RAISE NOTICE '产品数据进度: %/%', 
                LEAST((i + 1) * p_batch_size, total_products), total_products;
        END IF;
    END LOOP;
    
    RETURN 1;
EXCEPTION
    WHEN OTHERS THEN
        RAISE NOTICE '处理失败: %', SQLERRM;
        RETURN 0;
END;
$$;


----------------------------------
-- 调用存储函数
DO $$
DECLARE
    result INTEGER;
    json_data TEXT;
BEGIN
    json_data := '{
      "users": [
        {
          "id": 1,
          "name": "张三",
          "email": "zhangsan@example.com",
          "isActive": true
        },
        {
          "id": 2,
          "name": "李四",
          "email": "lisi@example.com",
          "isActive": false
        },
        {
          "id": 3,
          "name": "王五",
          "email": "wangwu@example.com",
          "isActive": true
        }
      ],
      "products": [
        {
          "productId": "P001",
          "productName": "笔记本电脑",
          "price": 5999.99,
          "category": "电子产品"
        },
        {
          "productId": "P002",
          "productName": "办公椅",
          "price": 899.5,
          "category": "家具"
        }
      ]
    }';
    
    result := InsertFromJSON(json_data);
    RAISE NOTICE '执行结果: %', result;
END
$$;


-- 查看用户表数据
SELECT count(0) FROM users;

-- 查看产品表数据
SELECT count(0) FROM products;



DO $$
DECLARE
    result INTEGER;
    json_data TEXT;
BEGIN
    json_data := '{"users": [{"id": 1, "name": "张三", "email": "zhangsan@example.com", "isActive": true}], "products": [{"productId": "P001", "productName": "笔记本电脑", "price": 5999.99, "category": "电子产品"}]}';
    result := InsertFromJSON(json_data);
    RAISE NOTICE '执行结果: %', result;
END
$$;



----------------------------------
-- 完整的测试代码（包含用户和产品表）
DO $$
DECLARE
    start_time TIMESTAMP;
    end_time TIMESTAMP;
    result INTEGER;
    big_json TEXT;
BEGIN
    start_time := clock_timestamp();
    
    -- 生成测试数据（3万条用户 + 1万条产品）
    SELECT json_build_object(
        'users', (
            SELECT json_agg(json_build_object(
                'id', g, 
                'name', '用户' || g,
                'email', 'user' || g || '@example.com',
                'isActive', CASE WHEN g % 10 != 0 THEN true ELSE false END
            )) FROM generate_series(1, 30000) g
        ),
        'products', (
            SELECT json_agg(json_build_object(
                'productId', 'P' || lpad(g::text, 5, '0'),
                'productName', '产品' || g,
                'price', (random() * 10000)::numeric(10,2),
                'category', CASE 
                    WHEN g % 4 = 0 THEN '电子产品'
                    WHEN g % 4 = 1 THEN '家具'
                    WHEN g % 4 = 2 THEN '服装'
                    ELSE '食品'
                END
            )) FROM generate_series(1, 10000) g
        )
    ) INTO big_json;
    
    result := InsertFromJSONBatch(big_json,10000);
    end_time := clock_timestamp();
    
    RAISE NOTICE '处理3万用户 + 1万产品记录耗时: %', end_time - start_time;
    RAISE NOTICE '执行结果: %', result;
    
    -- 验证插入结果
    RAISE NOTICE '用户表记录数: %', (SELECT count(*) FROM users);
    RAISE NOTICE '产品表记录数: %', (SELECT count(*) FROM products);
END 
$$;



--------------------------------
    SELECT json_build_object(
        'users', (
            SELECT json_agg(json_build_object(
                'id', g, 
                'name', '用户' || g,
                'email', 'user' || g || '@example.com',
                'isActive', CASE WHEN g % 10 != 0 THEN true ELSE false END
            )) FROM generate_series(1, 30000) g
        ),
        'products', (
            SELECT json_agg(json_build_object(
                'productId', 'P' || lpad(g::text, 5, '0'),
                'productName', '产品' || g,
                'price', (random() * 10000)::numeric(10,2),
                'category', CASE 
                    WHEN g % 4 = 0 THEN '电子产品'
                    WHEN g % 4 = 1 THEN '家具'
                    WHEN g % 4 = 2 THEN '服装'
                    ELSE '食品'
                END
            )) FROM generate_series(1, 10000) g
        )
    )
    
--------------------------------   pg9.2.4
CREATE OR REPLACE FUNCTION InsertFromJSON(p_json_data TEXT)
RETURNS INTEGER AS $$
DECLARE
    users_json TEXT;
    products_json TEXT;
    user_rec RECORD;
    product_rec RECORD;
    user_data JSON;
    product_data JSON;
BEGIN
    -- 9.2.4 需要手动解析JSON
    -- 提取users数组
    users_json := substring(p_json_data from '"users":\[(.*?)\]');
    products_json := substring(p_json_data from '"products":\[(.*?)\]');
    
    -- 手动解析用户数据
    FOR user_rec IN 
        SELECT * FROM regexp_split_to_table(
            substring(users_json from '^\[(.*)\]$'), 
            '},{'
        ) AS user_obj
    LOOP
        -- 手动提取每个字段（简化版，实际需要更复杂的解析）
        INSERT INTO users (id, name, email, isActive)
        VALUES (
            (substring(user_rec.user_obj from '"id":(\d+)'))::INTEGER,
            substring(user_rec.user_obj from '"name":"([^"]+)"'),
            substring(user_rec.user_obj from '"email":"([^"]+)"'),
            (substring(user_rec.user_obj from '"isActive":(true|false)'))::BOOLEAN
        );
    END LOOP;
    
    -- 类似解析产品数据...
    
    RETURN 1;
EXCEPTION
    WHEN OTHERS THEN
        RAISE NOTICE '操作失败: %', SQLERRM;
        RETURN 0;
END;
$$ LANGUAGE plpgsql;