sql - PL/pgSQL 中的第一次循环后错误游标不存在
问题描述
我需要将大量 csv 文件加载到 PostgreSQL 数据库中。我有一个表source_files
,其中包含文件路径和一个标志,该标志指示是否已经为我需要加载的所有 csv 文件加载了一个文件。
我编写了以下代码,它正确加载了第一个文件,但随后引发了错误:
ERROR: cursor "curs" does not exist
为什么我会收到此错误,我该如何解决?
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 $$;
解决方案
您的代码有一个大问题是COMMIT
. 您可以COMMIT
在语句中使用DO
,但是一旦事务结束,游标就会关闭。在 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
andCLOSE
语句都是不必要的。
推荐阅读
- java - Writing a program to check password for certain things
- python - Python: How to: attribute of an instance which depends on flag, which is itself an attribute of that instance / object
- javascript - How to get attribute value from Html through axios request?
- reactjs - Can't resolve export default from 'xxxx' in index.js vscode when ctrl/cmd click imports
- c++ - Constexpr: Convert a list of string views into a list of char arrays
- javascript - Complete and uncompleted todos
- python - Rank by group after sorting in pandas
- python-3.x - (Python3) Check if a variable is in an sqlite3 Database
- ios - Why is my hidden IOS button still selectable as an accessibility item?
- flutter - Flutter - How to get progress of a Loading Indicator?