PL/pgSQL中第一个循环后出现“游标不存在”的错误

Question

PL/pgSQL中第一个循环后出现“游标不存在”的错误

sqlpostgresqlplpgsql

3

我需要将大量的csv文件加载到PostgreSQL数据库中。我有一个名为source_files的表格，其中包含文件路径和标志，指示是否已经加载了所有需要加载的csv文件。

我编写了以下代码，可以正确加载第一个文件，但随后抛出错误：

ERROR: 游标“curs”不存在

为什么会出现这个错误，我该如何解决？

DO $$
DECLARE
    file_record record;
    curs CURSOR
        FOR SELECT id, file_path
            FROM source_files
            WHERE added_to_db=FALSE
            ORDER BY id;
BEGIN
    OPEN curs;
    LOOP
        -- Get next entry in source file which contains name of csv to load
        FETCH curs INTO file_record;
        exit WHEN NOT found;
        BEGIN
            -- As we need to add a column to the data after loading csv but before inserting
            -- into final table we use a temporary table mytemp
            DROP TABLE mytemp;

            CREATE TABLE mytemp
            (
                dataA numeric,
                dataB numeric
            );

            -- Load csv file   
            EXECUTE FORMAT('COPY mytemp
                            FROM ''%s''
                            DELIMITER '',''
                            CSV HEADER;', file_record.file_path);

            -- Add Column specifying what source file the data is from
            ALTER TABLE mytemp
                ADD COLUMN source_id int;

            UPDATE mytemp 
                SET source_id=file_record.id;

            -- Add the data to the destination table
            INSERT INTO data_table(
                dataA,
                dataB,
                source_id
            )
            SELECT 
                mytemp.dataA,
                mytemp.dataB
                mytemp.source_id
            FROM 
                mytemp

            -- Set a flag to indicate that the current file in source_files has been loaded
            UPDATE source_files
                SET added_to_db=TRUE WHERE CURRENT OF curs;

            COMMIT;
        END;

    END LOOP;
    CLOSE curs;

END $$;

- sev

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Laurenz Albe · Accepted Answer

你的代码中存在一个很大的问题，就是 COMMIT。你可以在 DO 语句中使用 COMMIT，但是一旦事务结束，光标就被关闭了。在SQL中，你可以创建一个 WITH HOLD 的光标，它在事务结束后仍然有效，但在PL/pgSQL中不可用。

我建议删除 COMMIT。

你代码中的另一个错误是你使用了 format 函数，这会让你遭受SQL注入攻击。代替之前的用法，请使用

FORMAT('COPY mytemp
        FROM ''%s''
        DELIMITER '',''
        CSV HEADER;', file_record.file_path);

使用

FORMAT('COPY mytemp
        FROM %L
        DELIMITER '',''
        CSV HEADER;', file_record.file_path);

通过在循环中使用隐式光标，可以使代码变得更加简单：

FOR file_record IN
   SELECT id, file_path
   FROM source_files
   WHERE added_to_db=FALSE
   ORDER BY id
LOOP
   ...
END LOOP;

这样可以避免声明游标和EXIT WHEN。而且，OPEN和CLOSE语句都是不必要的。