The entire script will fail if, at any point, one of the commands gives an error (or higher) message. Almost all of the SQL used to define objects (DDL) allows a way to avoid throwing errors. More precisely, commands that begin with the DROP keyword have an IF EXISTS option. This allows you to execute the DROP keyword, whether or not the object already exists.
Thus, by the end of the command, that object will not exist:
DROP VIEW IF EXISTS cust_view;
Similarly, most commands that begin with the CREATE keyword have the optional OR REPLACE suffix. This allows the CREATE statement to overwrite the definition if one already exists, or add the new object if it didn't exist yet, like this:
CREATE OR REPLACE VIEW cust_view AS SELECT * FROM cust;
In the cases where both the DROP IF EXISTS and CREATE OR REPLACE options exist, you might think that CREATE OR REPLACE is usually sufficient. However, if you change the output definition of a function or a view, then using OR REPLACE is not sufficient. In that case, you must use DROP and recreate it, as shown in the following example:
postgres=# CREATE OR REPLACE VIEW cust_view AS
SELECT col as title1 FROM cust;
CREATE VIEW
postgres=# CREATE OR REPLACE VIEW cust_view
AS SELECT col as title2 FROM cust;
ERROR: cannot change name of view column "title1" to "title2"
Note also that CREATE INDEX does not have an OR REPLACE option. If you run it twice, you'll get two indexes on your table, unless you specifically name the index. There is a DROP INDEX IF EXISTS option, but it may take a long time to drop and recreate an index. An index exists just for the purpose of optimization, and it does not change the actual result of any query, so this different behavior is actually very convenient. This is also reflected in the fact that the SQL standard doesn't mention indexes at all, even though they exist in practically all database systems, because they do not affect the logical layer.
PostgreSQL does not support nested transaction control commands, which can lead to unexpected behavior. For instance, consider the following code, written in a nested transaction style:
postgres=# BEGIN;
BEGIN
postgres=# CREATE TABLE a(x int);
CREATE TABLE
postgres=# BEGIN;
WARNING: there is already a transaction in progress
BEGIN
postgres=# CREATE TABLE b(x int);
CREATE TABLE
postgres=# COMMIT;
COMMIT
postgres=# ROLLBACK;
NOTICE: there is no transaction in progress
ROLLBACK
A hypothetical author of such code probably meant to create table a first, and then create table b. Then, they changed their mind and rolled back both the inner transaction and the outer transaction. However, what PostgreSQL does is discard the second BEGIN statement so that the COMMIT statement is matched with the first BEGIN statement and the inner transaction becomes a top-level transaction. Hence, right after the COMMIT statement, we are outside a transaction block, so the next statement is assigned its own transaction. When ROLLBACK is issued as the next statement, PostgreSQL notices that the transaction is actually empty.
The danger in this particular example is that the user inadvertently committed a transaction, thus waving the right to roll it back, although we should say that a careful user would have noticed the warning and paused to think before going ahead.
From this example, you learn a valuable lesson: if you have used transaction control commands in your script, then wrapping them again in a higher-level script or command can cause problems of the worst kind, such as committing stuff that you wanted to roll back. This is important enough to deserve a boxed warning.