postgresql - Postgress 函数无法与 LOOP 和 INTO 一起按预期工作

Question

我有以下程序

CREATE OR REPLACE FUNCTION createtestdata(
    )
    RETURNS boolean
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
AS $BODY$
    DECLARE
        proj RECORD;
        ruleid integer;
        emailtemplateid integer;
        otherTemplates RECORD;
        markdoenTemplateId integer;
        pdfTemplateId integer;
        jsonTemplateId integer;
    BEGIN
    

        FOR proj IN
            SELECT p.id AS project_id FROM project p
            WHERE p.id != 10409 and p.projecttype='software'
            ORDER BY p.id
        LOOP
            select  "ID" from "TEST_TEMPLATES" 
            where "PROJECT_ID" = proj.project_id AND "TYPE" =1 ORDER BY RANDOM() LIMIT 1
            INTO emailtemplateid;
            RAISE NOTICE 'template id is %...', emailtemplateid;

            select  "ID", "TYPE" from "TEST_TEMPLATES" 
            where "PROJECT_ID" = proj.project_id AND "TYPE" IN (2,4,5)
            ORDER BY RANDOM() LIMIT 1
            INTO otherTemplates;

            RAISE NOTICE 'OTHER TEMPLATES %',   otherTemplates; 
            RAISE NOTICE 'inserting records for project id %...', proj.project_id;

            pdfTemplateId = NULL;
            markdoenTemplateId = NULL;
            jsonTemplateId = NULL;

            IF otherTemplates."TYPE" = 2 THEN
                pdfTemplateId = otherTemplates."ID";
            ELSIF otherTemplates."TYPE" = 4 THEN
                markdoenTemplateId = otherTemplates."ID";
            ELSIF otherTemplates."TYPE" = 5 THEN
                jsonTemplateId = otherTemplates."ID";
            END IF;
            RAISE NOTICE 'PDF TEMP IS %... MKDN TEMP IS %... JSON TEMP IS %', 
            pdfTemplateId, markdoenTemplateId, jsonTemplateId;  

            INSERT INTO "TEST_RULES"(
                "BEFORE_NOF_DAYS", "CREATED", "CREATED_BY", "CRON_EXPRESSION", "IS_ACTIVE", 
                "IS_DELETED", "MODIFIED", "MODIFIED_BY", "NAME", 
                "PROJECT_ID", "SCHEDULE_TIME", "TRIGGER_TYPE", "VERSION_PATTERN")
            VALUES (null, now(), 'admin', null, true, 
                    false, now(), 'admin', 'rule for release project ' || proj.project_id, 
                    proj.project_id, null, 2, null) 
            RETURNING "ID" INTO ruleId;

            RAISE NOTICE 'Inserting email action for Rule id just created is %...', ruleId;


            
            INSERT INTO "TEST_RULE_ACTIONS"(
                "ACTION_TYPE", "CONFLUENCE_INSTANCE_ID", "CREATED", "CREATED_BY", "EMAIL_FROM", 
                "EXTERNAL_USERS", "IS_DELETED", "JIRA_GROUPS", "JIRA_USERS", "JSON_TEMPLATE_ID", "MARKDOWN_TEMPLATE_ID",
                "MODIFIED", "MODIFIED_BY", "NAME", 
                "PAGE_LABEL", "PARENT_ID", "PDF_TEMPLATE_ID", "PORTAL_ID", "POST_END_POINT", "POST_PASSWORD", 
                "POST_TEMPLATE_TYPE", "POST_TYPE", "POST_USER_NAME", "REPLY_TO", "RULE_ID", 
                "SEND_EMAIL_AS_BCC", "SEQUENCE_NUMBER", "SPACE_KEY", "TEMPLATE_ID", "TEMPLATE_TYPE_ID")
            VALUES (1, NULL, NOW(), 'admin', '[e-mail]', 
                '', false, 'arn-users', 'admin', jsonTemplateId, markdoenTemplateId, 
                now(), 'admin', 'email action configured for project ' || proj.project_id, 
                NULL, 0, pdfTemplateId, 0, NULL, NULL, 
                NULL, 'page', NULL, '[e-mail]', ruleId, 
                    false, 1, NULL, emailtemplateid, 1);

        END LOOP;

        RETURN true;
    END;
$BODY$;

当我运行该函数时，我得到了输出

psql:dproc.sql:122: NOTICE:  template id is 3425...
psql:dproc.sql:122: NOTICE:  project id 10494...
psql:dproc.sql:122: NOTICE:  OTHER TEMPLATES (1709,5)
psql:dproc.sql:122: NOTICE:  PDF TEMP IS <NULL>... MKDN TEMP IS <NULL>... JSON TEMP IS 1709
psql:dproc.sql:122: NOTICE:  inserting records for project id 10497...
**psql:dproc.sql:122: NOTICE:  Inserting email action for Rule id just created is <NULL>...**

最后一行“为刚刚创建的规则 ID 插入电子邮件操作是”是错误的，插入语句在我的本地 SQL 上正常工作。但这不适用于 AWS postgres

此外，如果我将插入语句放在循环中的第一个语句中，它就可以工作。但是其他两个语句不起作用

标签： postgresqlfunctionplpgsql