Creating anonymous block on Postgres to update rows

Luis Louis 0
Viewed 37

I ran this anonymous block on Postgres and got this error:

DO
$$BEGIN
    FOR diag_file IN 
        SELECT d_file.diagnostic_file_id AS diagnostic_file_id, 
            controller.controller_id AS controller_id
        FROM diagnostic_file d_file
        JOIN decoder_mapping d_mapping ON d_mapping.decoder_mapping_id = d_file.decoder_mapping_id
        JOIN device_model d_model ON d_model.device_model_id = d_mapping.device_model_id
        JOIN controller ON controller.device_model_id = d_model.device_model_id
        WHERE d_file.controller_id IS NULL
    LOOP
        UPDATE diagnostic_file SET controller_id = diag_file.controller_id WHERE diagnostic_file.diagnostic_file_id = diag_file.diagnostic_file_id;
        COMMIT;
    END LOOP;
END;$$;

An It returns me this error:

WARNING:  there is no transaction in progress

Query 1 OK: COMMIT
postgresql-9.5 postgresql
1 Answers

An It returns me this error:

WARNING: there is no transaction in progress

Not an error, a warning. You tried to commit, but there's no transaction.

Often this happens when you've turned on autocommit and then commit. It's warning you that the commit does nothing.

Your loop should not work at all in PostgreSQL 9.5, you can't begin nor end transactions inside PL/pgSQL in that version. THat is not introduced until PostgreSQL 11.

ERROR:  cannot begin/end transactions in PL/pgSQL
HINT:  Use a BEGIN block with an EXCEPTION clause instead.
CONTEXT:  PL/pgSQL function inline_code_block line 11 at SQL statement

If you're writing a loop in SQL, you can probably do it easier and faster without the loop. In this case with an update from a sub-select.

update diagnostic_file set controller_id = diag_file.controller_id
from (
  SELECT d_file.diagnostic_file_id AS diagnostic_file_id, 
         controller.controller_id AS controller_id
  FROM diagnostic_file d_file
  JOIN decoder_mapping d_mapping ON d_mapping.decoder_mapping_id = d_file.decoder_mapping_id
  JOIN device_model d_model ON d_model.device_model_id = d_mapping.device_model_id
  JOIN controller ON controller.device_model_id = d_model.device_model_id
  WHERE d_file.controller_id IS NULL
) diag_file
WHERE diagnostic_file.diagnostic_file_id = diag_file.diagnostic_file_id

The update docs have many examples.

Schwern0
Related