postgresql - 如何修复此 PostgreSQL 9.1 存储过程？

Question

我们的软件有问题，为了解决这个问题，我必须编写一个存储过程，该过程将作为升级安装的升级过程的一部分运行。此存储过程需要在特定表中查找与特定条件匹配的每一行并更新该行。由于内部原因，更新必须通过我们专门为插入和更新数据编写的存储过程来完成。

这是我为解决此问题而编写的存储过程:

CREATE OR REPLACE FUNCTION FixDataProblem() RETURNS VOID AS $$
DECLARE
    FixCursor   NO SCROLL CURSOR FOR
        SELECT * FROM MyTable WHERE ProblemColumn IN ( '?', 'PR' );
    RowToUpdate         MyTable%ROWTYPE;
BEGIN
    -- Open the cursor
    OPEN FixCursor;

    -- Start a loop
    LOOP
        -- Fetch the next row from thr cursor
        FETCH FixCursor INTO RowToUpdate;

        -- Did we get anything back?
        IF RowToUpdate IS NULL THEN
            -- We didn't. Exit the loop
            EXIT;
        END IF;

        -- Call the UpsertMyTable stored procedure to set the ProblemColumn column to NULL
        SELECT CarSystem.UpsertMyTable( RowToUpdate.RowId,
                               RowToUpdate.ForeignId,
                               RowToUpdate.CountryId,
                               NULL,
                               RowToUpdate.Plate,
                               RowToUpdate.HashedData,
                               RowToUpdate.PlateClassId,
                               RowToUpdate.AlarmClassId,
                               RowToUpdate.BeginDate,
                               RowToUpdate.EndDate,
                               RowToUpdate.ListPriorityId,
                               RowToUpdate.VehicleTypeId,
                               RowToUpdate.MakeId,
                               RowToUpdate.ModelId,
                               RowToUpdate.Year,
                               RowToUpdate.ColorId,
                               RowToUpdate.Notes,
                               RowToUpdate.OfficerNotes,
                               NULL,
                               UUID_GENERATE_V4() );
    END LOOP;

    -- Close the cursor
    CLOSE ListDetailsCursor;
END;
$$ LANGUAGE plpgsql;

这个存储过程很好，但是当我运行它时，我得到:

ERROR:  query has no destination for result data
HINT:  If you want to discard the results of a SELECT, use PERFORM instead.
CONTEXT:  PL/pgSQL function "fixdataproblem" line 22 at SQL statement


********** Error **********

ERROR: query has no destination for result data
SQL state: 42601
Hint: If you want to discard the results of a SELECT, use PERFORM instead.
Context: PL/pgSQL function "fixdataproblem" line 22 at SQL statement

我该如何解决这个问题？我相信我正在正确调用存储过程。我真的不知道这个存储过程有什么问题。

谢谢

托尼

Answer 1

上面写着:

ERROR:  query has no destination for result data
HINT:  If you want to discard the results of a SELECT, use PERFORM instead.
CONTEXT:  PL/pgSQL function "fixdataproblem" line 22 at SQL statement

第 22 行:

    -- Call the UpsertMyTable stored procedure to set the ProblemColumn column to NULL
    SELECT CarSystem.UpsertMyTable( RowToUpdate.RowId,
    ...

将其从 SELECT 更改为 PERFORM。参见 PERFORM为什么。