PostgreSQL ALTER PROCEDURE语法错误排查求助

报错信息 #

ERROR: [GET_ALL_DATASETS_BY_DATE] syntax error at or near "LANGUAGE"
LINE 3:         LANGUAGE SQL

问题代码 #

def PROCEDURE_GET_ALL_DATASETS_BY_DATE(conn):
    print("CREATING [GET_ALL_DATASETS_BY_DATE] PROCEDURE")
    try:
        cursor = conn.cursor()
        cursor.execute(f"""
        ALTER PROCEDURE GET_ALL_DATASETS_BY_DATE(how_many int)
        LANGUAGE SQL
        AS $$
        SELECT U.username, F.File_PATH "Path", Description "Desc", F.Date_Time "Date", F.File_size "Size"
            FROM USERS as U
            INNER JOIN FILES as F
            ON F.UserId = U.User_Id
            ORDER BY F.Date_Time
            limit how_many  
            $$;
        """)
        conn.commit()
    except Exception as e:
        cursor.execute("ROLLBACK")
        print("ERROR: [GET_ALL_DATASETS_BY_DATE] " + str(e))

错误原因 #

• ALTER PROCEDURE用法错误：PostgreSQL中ALTER PROCEDURE仅能修改存储过程的非核心属性（如权限、名称），无法直接替换存储过程的代码逻辑或指定LANGUAGE。要更新存储过程的核心定义，必须使用CREATE OR REPLACE PROCEDURE语句。
• PROCEDURE与FUNCTION混淆：你的代码试图直接通过SELECT返回结果集，但PostgreSQL的PROCEDURE默认不支持这种写法（需借助游标或输出参数实现）。如果仅需返回查询数据集，使用FUNCTION是更适配的选择，写法更简洁。

修复方案 #

方案1：改用CREATE OR REPLACE PROCEDURE（适配PROCEDURE的返回逻辑） #

如果坚持使用PROCEDURE，需要通过游标来返回结果：

CREATE OR REPLACE PROCEDURE GET_ALL_DATASETS_BY_DATE(how_many int, OUT result refcursor)
LANGUAGE plpgsql
AS $$
BEGIN
    OPEN result FOR
        SELECT U.username, F.File_PATH "Path", F.Description "Desc", F.Date_Time "Date", F.File_size "Size"
        FROM USERS as U
        INNER JOIN FILES as F ON F.UserId = U.User_Id
        ORDER BY F.Date_Time
        LIMIT how_many;
END;
$$;

调用时需处理游标：

CALL GET_ALL_DATASETS_BY_DATE(10, 'result_cursor');
FETCH ALL FROM result_cursor;

方案2：改用FUNCTION（更贴合你的查询需求） #

这是更推荐的方案，FUNCTION可以直接返回查询结果：

CREATE OR REPLACE FUNCTION GET_ALL_DATASETS_BY_DATE(how_many int)
RETURNS TABLE(
    username text,
    "Path" text,
    "Desc" text,
    "Date" timestamp,
    "Size" bigint
)
LANGUAGE SQL
AS $$
    SELECT U.username, F.File_PATH, F.Description, F.Date_Time, F.File_size
    FROM USERS as U
    INNER JOIN FILES as F ON F.UserId = U.User_Id
    ORDER BY F.Date_Time
    LIMIT how_many;
$$;

调用方式：

SELECT * FROM GET_ALL_DATASETS_BY_DATE(10);

修改后的Python代码示例（基于方案2） #

def PROCEDURE_GET_ALL_DATASETS_BY_DATE(conn):
    print("CREATING/UPDATING [GET_ALL_DATASETS_BY_DATE] FUNCTION")
    try:
        cursor = conn.cursor()
        cursor.execute("""
        CREATE OR REPLACE FUNCTION GET_ALL_DATASETS_BY_DATE(how_many int)
        RETURNS TABLE(
            username text,
            "Path" text,
            "Desc" text,
            "Date" timestamp,
            "Size" bigint
        )
        LANGUAGE SQL
        AS $$
            SELECT U.username, F.File_PATH, F.Description, F.Date_Time, F.File_size
            FROM USERS as U
            INNER JOIN FILES as F ON F.UserId = U.User_Id
            ORDER BY F.Date_Time
            LIMIT how_many;
        $$;
        """)
        conn.commit()
    except Exception as e:
        cursor.execute("ROLLBACK")
        print("ERROR: [GET_ALL_DATASETS_BY_DATE] " + str(e))

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