Writing do statements

Commonly when cleaning data, we'll get data that will have bad dates in it. This would cause an exception and halt our SQL statement; however, by using a DO function with an exception handler, our statement will run to completion. Let's see how we can handle that type of exception with the patients table and the created_on column. This will also give us a chance to use a DO style function.

Diese Übung ist Teil des Kurses

Transactions and Error Handling in PostgreSQL

Kurs anzeigen

Anleitung zur Übung

Create a DO function to initiate catching an exception.
BEGIN a transaction where you INSERT the row (a1c = 5.8, glucose = 89, fasting = TRUE, and created_on = '37-03-2020 01:15:54') into patients.
Add an EXCEPTION handler, that inserts 'bad date' in the detail column of the errors table in case of an error.
Specify the 'plpgsql' language .

Interaktive Übung

Vervollständige den Beispielcode, um diese Übung erfolgreich abzuschließen.

-- Create a DO $$ function
____ ____
-- BEGIN a transaction block
BEGIN 
    INSERT INTO patients (a1c, glucose, fasting, created_on) 
    VALUES (____, ____, ____, '37-03-2020 01:15:54');
-- Add an EXCEPTION
___
-- For all all other type of errors
WHEN others THEN 
    INSERT INTO errors (msg, detail) 
    VALUES ('failed to insert', '____');
END;
-- Make sure to specify the language
$$ language '____';

-- Select all the errors recorded
SELECT * FROM errors;

Code bearbeiten und ausführen