将GeoJSON数据导入PostgreSQL时遇函数不存在错误求助

我需要将指定目录下的多个GeoJSON文件导入PostgreSQL的states表，编写了如下PL/pgSQL函数遍历目录读取文件并插入数据：

CREATE OR REPLACE FUNCTION insert_geojson_data(directory text) RETURNS void AS $$
DECLARE
    file_name text;
    geojson_data json;
begin
    -- Loop through GeoJSON files in the specified directory
    FOR file_name IN 
        SELECT *
        FROM pg_ls_dir(directory)
    loop
        -- Read GeoJSON data from file
        IF file_name LIKE '%.geojson' then
       
        RAISE NOTICE 'GeoJSON data: %', pg_read_file(directory || '/' || file_name);
        EXECUTE 'SELECT jsonb_agg(features) FROM jsonb_array_elements_text(($1::jsonb)->>''features'')'
            INTO geojson_data
            USING pg_read_file(directory || '/' || file_name);

        -- Insert data into the states table
        INSERT INTO states (shapeId, name, geometry)
        select
            geojson_data->>'properties'->>'shapeId',
            geojson_data->>'properties'->>'name',
            ST_GeomFromGeoJSON(geojson_data->>'geometry');
           
        RAISE NOTICE 'Data from % inserted successfully', file_name;
        END IF;
    END LOOP;
    RETURN;
END;
$$ LANGUAGE plpgsql;

执行SELECT public.insert_geojson_data('path/to/directory');时，RAISE NOTICE能正常打印GeoJSON内容，但触发以下错误：

SQL Error [42883]: ERROR: function jsonb_array_elements_text(text) does not exist
  Hint: No function matches the given name and argument types. You might need to add explicit type casts.
  Where: PL/pgSQL function insert_geojson_data(text) line 15 at EXECUTE

问题集中在这段代码：

EXECUTE 'SELECT jsonb_agg(features) FROM jsonb_array_elements_text(($1::jsonb)->>''features'')'
            INTO geojson_data
            USING pg_read_file(directory || '/' || file_name);

错误原因 #

1. 类型不匹配：jsonb_array_elements_text()要求参数为jsonb类型，但($1::jsonb)->>'features'返回的是text类型（->>操作符会把JSON值转为文本），导致函数调用失败。
2. 逻辑错误：GeoJSON的features是数组结构，需要遍历每个Feature对象插入，而非把整个数组聚合后当成单个Feature处理。

修正后的函数 #

CREATE OR REPLACE FUNCTION insert_geojson_data(directory text) RETURNS void AS $$
DECLARE
    file_name text;
    geojson_jsonb jsonb;
begin
    -- 遍历指定目录下的文件
    FOR file_name IN 
        SELECT * FROM pg_ls_dir(directory)
    LOOP
        IF file_name LIKE '%.geojson' THEN
            -- 读取文件并转为jsonb类型
            geojson_jsonb := pg_read_file(directory || '/' || file_name)::jsonb;
            
            -- 遍历features数组中的每个元素，插入到states表
            INSERT INTO states (shapeId, name, geometry)
            SELECT
                (feature->'properties'->>'shapeId')::text,
                (feature->'properties'->>'name')::text,
                ST_GeomFromGeoJSON(feature->'geometry'::text)
            FROM jsonb_array_elements(geojson_jsonb->'features') AS feature;
            
            RAISE NOTICE 'Data from % inserted successfully', file_name;
        END IF;
    END LOOP;
    RETURN;
END;
$$ LANGUAGE plpgsql;

关键改动说明 #

1. 直接将读取的文件内容转为jsonb类型存储，避免重复转换操作。
2. 使用jsonb_array_elements()替代jsonb_array_elements_text()，直接获取每个Feature的JSON对象而非文本。
3. 直接在INSERT语句中遍历features数组，逐个插入每个Feature的属性和几何数据，无需额外聚合操作。
4. 去掉了不必要的EXECUTE动态SQL，简化代码逻辑的同时提升性能。

内容的提问来源于stack exchange，提问作者Shantanu Tomar