plpgsql语法错误在“;”附近
我已经查看了
this个类似问题,但我认为我的问题可能有所不同.如果我正确理解他们的问题,问题是由上游的语法错误引起的.
在我的情况下,语法错误是如此接近程序的开头,它给了我一个可能出错的窗口,但是一切看起来都没问题. 码: DO $$ DECLARE topic_cursor CURSOR FOR SELECT * FROM "socialMediaModel_topic" WHERE "active_search"=True; BEGIN OPEN topic_cursor; FETCH FIRST FROM topic_cursor; LOOP SELECT "topic" FROM topic_cursor AS "c_topic"; SELECT "topic_id" FROM topic_cursor AS "c_id"; SELECT "active_search" FROM topic_cursor AS "c_active"; INSERT INTO "socialMediaModel_datacollection" ("name","active") VALUES (c_topic,c_active); INSERT INTO "socialMediaModel_datacollectiontopic" ("data_collection_id_id","topic_id_id") VALUES ((SELECT "data_collection_id" FROM "DataCollection" where name=c_topic),c_id); FETCH NEXT FROM topic_cursor; END LOOP; CLOSE topic_cursor; UPDATE "socialMediaModel_topic" SET "active_search" = False WHERE "active_search"=True; COMMIT; END$$; 错误: ERROR: syntax error at or near ";" LINE 9: FETCH FIRST FROM topic_cursor; ^ ********** Error ********** ERROR: syntax error at or near ";" SQL state: 42601 Character: 247 在编写此脚本时,我几乎完全遵循这些资源: > PostgreSQL Documentation: Cursors 数据库:PostgreSQL 9.1 如果我错过了一些非常明显的事情,我会提前道歉.我一整天都在盯着这个剧本,所以我的大脑可能会有点乱. 解决方法
程序解决方案
您的代码中存在许多问题. DO $do$ DECLARE rec record; BEGIN FOR rec IN SELECT s.*,d.data_collection_id FROM "socialMediaModel_topic" s LEFT JOIN "DataCollection" d ON d.name = s.topic WHERE active_search LOOP INSERT INTO "socialMediaModel_datacollection" (name,active) VALUES (rec.topic,rec.active_search); INSERT INTO "socialMediaModel_datacollectiontopic" (data_collection_id_id,topic_id_id) VALUES (rec.data_collection_id,rec.topic_id); END LOOP; UPDATE "socialMediaModel_topic" SET active_search = FALSE WHERE active_search; END $do$; 主要观点 > 基于集合的解决方案 整个程序方法不如data-modifying CTEs的基于集合的方法: WITH ins1 AS ( INSERT INTO "socialMediaModel_datacollection" (name,active) SELECT topic,active_search FROM "socialMediaModel_topic" WHERE active_search ),ins2 AS ( INSERT INTO "socialMediaModel_datacollectiontopic" (data_collection_id_id,topic_id_id) SELECT d.data_collection_id,s.topic_id FROM "socialMediaModel_topic" s LEFT JOIN "DataCollection" d ON d.name = s.topic WHERE s.active_search ) UPDATE "socialMediaModel_topic" SET active_search = FALSE WHERE active_search; 或者,如果您有并发写入加载,请使用FOR UPDATE以避免竞争条件: WITH sel AS ( SELECT s.topic_id,s.topic,s.active_search,d.data_collection_id FROM "socialMediaModel_topic" s LEFT JOIN "DataCollection" d ON d.name = s.topic WHERE s.active_search FOR UPDATE ),ins1 AS ( INSERT INTO "socialMediaModel_datacollection" (name,active_search FROM sel ),s.topic_id FROM sel ) UPDATE "socialMediaModel_topic" SET active_search = FALSE WHERE active_search; 更多关于SELECT … FOR CTD更新: 类似的问题/答案: (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |
- Windows Azure Centos映像:无法升级到6.4
- windows – 批处理脚本传递多个参数进行调用
- .net-4.0 – Microsoft图表堆积柱形图存在差距
- windows – 我可以在git中修改一个名称取决于操作系统的路径
- windows-server-2003 – 如何获得创建全文索引的权限?
- windows-7 – Windows 7上的OpenVPN客户端,数据包未路由
- windows – 我在哪里(安全地)获取libintl3.dll?
- macos – 列出所有应用程序的所有窗口
- windows-phone-8 – Windows Embedded 8掌上电脑(WE8H)支持
- windows-server-2012-r2 – 2节点集群中的Hyper-v故障转移或
- windows – 从光学介质中恢复,忽略读取错误
- windows-xp – 如何在多台Windows XP计算机上远程
- windows安装composer
- .net – 如何在FileSavePicker中接受任何文件类型
- Microsoft Git Provider和Visual Studio 2012无法
- Windows API一日一练 5 RegisterClass和Register
- windows-server-2008 – 如何在不属于域的Window
- 解决windows64位系统上安装mysql-python报错
- 如何在Windows 7上安装PHP 7的MongoDB驱动程序?
- windows-server-2012 – Server 2012,服务器管理