使用psycopg2调用PostGIS扩展函数报UndefinedFunction错误求助

核心原因 #

从你的psql排查结果来看，当前连接的world数据库根本没有安装PostGIS扩展：

• \dx仅显示plpgsql，说明PostGIS未在该数据库中创建；
• 你提到的"执行SELECT * FROM pg_extension可验证"大概率是在其他数据库执行的，或者操作时选错了库，导致误判。

PostGIS扩展未安装的情况下，自然找不到public.ST_Centroid这类函数，和权限无关。

分步解决方案 #

1. 正确在world数据库安装PostGIS #

打开psql终端，执行以下步骤：

1. 连接到world数据库：

psql -U postgres -d world

2. 安装PostGIS扩展：

CREATE EXTENSION postgis;

3. 验证安装结果：执行\dx，应该能看到postgis扩展出现在列表中；同时执行\df public.ST_Centroid，会显示该函数的详细信息。

2. 修复代码中的连接与查询问题 #

你的代码存在两个潜在问题：

• 每次循环schema都新建连接，但未及时关闭，会导致连接泄漏；
• 设置search_path为单个schema后，虽然用public.ST_Centroid指定了函数的schema，但可以优化search_path避免硬编码：

修改后的代码示例：

import psycopg2

SCHEMAS = ["a", "b", "c", ...]
FROM_TABLES = ["foo","bar", ...]

for schema in SCHEMAS:    
    conn = psycopg2.connect(
        host='localhost',
        database='world',
        user="postgres",
        password="postgres",
        port=5432,
        # 同时加入当前schema和public，无需硬编码public.前缀调用函数
        options=f"-c search_path={schema},public"
    )

    try:
        for table in FROM_TABLES:
            with conn.cursor() as cursor:
                # 普通查询
                cursor.execute(f"""SELECT * FROM {table} LIMIT 10""")
                
                # 调用PostGIS函数，无需加public.前缀
                cursor.execute(f"""
                    SELECT ST_Centroid(geom) AS geom, way_id, osm_type, name FROM {table};
                """)
                # 如果需要处理查询结果，在这里添加fetch操作
                # results = cursor.fetchall()
        # with conn块会自动提交，无需额外commit
    finally:
        # 每次循环结束关闭连接，避免泄漏
        conn.close()

3. 额外验证点 #

如果安装PostGIS时提示"extension already exists"，说明你之前在其他数据库安装过，确认当前连接的是world库即可。

内容的提问来源于stack exchange，提问作者four-eyes