我使用的是SQL10.5,我有以下PostgreSQL:
FOR temprow IN
SELECT o.objectid, o.nametag, cor.userseqno, cor.commseqno
FROM "commuserobjectrights" as cor
LEFT JOIN "object" as o ON cor.objectid = o.objectid
WHERE o.nametag LIKE 'commission.video_questions'
LOOP
INSERT INTO u commuserobjectrights (objectid, commseqno, userseqno, access)
VALUES (temprow.objectid, temprow.commseqno, temprow.userseqno, TRUE);
END LOOP;
这将抛出以下错误:
ERROR: syntax error at or near "FOR" Position: 3
我以前从未使用过循环,但根据文档,postgresql应该支持这些类型的循环。是的,我已经一遍又一遍地检查了所有的表名和列名是否拼写正确。
发布于 2018-09-03 22:40:37
您不能在过程代码之外使用FOR
循环。但是,一般而言,Postgres (和SQL)已经进行了优化,可以执行基于集合的操作。因此,您可以将其表述为INSERT INTO ... SELECT
INSERT INTO commuserobjectrights (objectid, commseqno, userseqno, access)
SELECT o.objectid, o.nametag, cor.userseqno, TRUE
FROM "commuserobjectrights" as cor
LEFT JOIN "object" as o ON cor.objectid = o.objectid
WHERE o.nametag LIKE 'commission.video_questions';
发布于 2018-09-04 07:03:58
FOR
是过程代码,您不需要使用DO
或在存储代码中使用它。
DO $$
DECLARE
temprow record ;
BEGIN
FOR temprow IN
SELECT o.objectid, o.nametag, cor.userseqno, cor.commseqno
FROM "commuserobjectrights" as cor
LEFT JOIN "object" as o ON cor.objectid = o.objectid
WHERE o.nametag LIKE 'commission.video_questions'
LOOP
INSERT INTO commuserobjectrights (objectid, commseqno, userseqno, access)
VALUES (temprow.objectid, temprow.commseqno, temprow.userseqno, TRUE);
END LOOP;
END;
$$;
这不是完成此任务的最有效方法,但对于其他您不能轻松编写SQL的任务,do可能会很有用。
https://stackoverflow.com/questions/52151855
复制相似问题