PostgreSQL – How to Hold Text with Multiple Lines in psql Variable

There is an option in psql which stops executing commands on error, this is ON_ERROR_STOP. If we could raise an error somehow, this would do what we want.

The problem is that we have to test the variable and produce an error somehow. Since one can't use control structures in psql (because there are none)*, my only idea was to use SQL for testing. Well, producing an error conditionally is something which pl/pgsql is quite good at, so I wrote a function which would generate an error. I can now call this function from a simple CASE structure. A simple example:

-- let's assume for clarity that there is no function with this name in the database
CREATE OR REPLACE FUNCTION error_generator()
RETURNS boolean AS
$body$
BEGIN
    RAISE 'Meaningful error message here';
    RETURN FALSE; -- just for aesthetical purposes
END;
$body$
LANGUAGE plpgsql;

\set ON_ERROR_STOP on

BEGIN;

-- test for the variable value
-- notice that if :var is not set, it fails as well (with a syntax error)
SELECT CASE WHEN 1 = :var THEN error_generator() ELSE TRUE END;

INSERT INTO test_table (integer_value, text_value)
VALUES (:var, 'something');

COMMIT;

*: You can use any shell commands after \! and conditionals of the shell, but since \! opens a new shell, executing anything there does not have any effect for the current psql script.

@dezso had the completely right idea:

All this means that some data has been updated, right? Try to update them back, using a temp table where you copy the original data

The only thing left now was to make it happen.

So here's what I did. I took a leaf out of his book and manually edited the dump-file to use a table named table_backup. Then I created said table using the definition provided in my pgAdmin (but it can be done manually, too).

I left out triggers and constraints, as well as Foreign Keys, and then proceeded to "updating" the original table with the data from the backup table like the following:

BEGIN TRANSACTION;
ALTER TABLE table DISABLE TRIGGER ALL;

UPDATE table SET 
    (column1, column2, ...) = 
    (table_backup.column1, table_backup.colum2, ...)
FROM table_backup WHERE table.pk_column = table_backup.pk_column;

ALTER TABLE table ENABLE TRIGGER ALL;
-- I didn't but you can drop table_backup here
COMMIT;

So I am finally back with my original data, ready for the next testrun ;)