Interactive PostgreSQL Script

Do you want an input from end user, while running PostgreSQL script.

No Problem, here is the one of the solution for you.

In the following example, i am taking the confirmation from an end user, before dropping an existing database called "sample".

-- When any exception raised from this script, the complete script execution is going to fail.

-- We will be raising a custom exception, when the Drop DB != 'y|Y';
--
\set ON_ERROR_STOP on

-- Take input from the user.
--
\prompt 'Are you sure to drop ' Do_You_Want_To_Drop_Db

-- Get the user input.
--
\set Do_Drop_Db '\'' :Do_You_Want_To_Drop_Db '\''

-- Creating a temp table to store the value of the user confirmation.
-- This will be dropped when the session got closed.
--
CREATE TEMP TABLE Drop_Db AS SELECT :Do_Drop_Db::text AS confirm;

DO
$$
BEGIN
IF EXISTS(SELECT * FROM Drop_Db WHERE confirm NOT IN('y', 'Y')) THEN
RAISE EXCEPTION 'Database won''t drop. Hence closing this session';
ELSE
RAISE NOTICE 'Database will be droped. Please make sure the connection of this databases get closed.';
END IF;
END
$$;

DROP DATABASE sample;

Testing the Script

bash-4.1$ ../bin/psql -U postgres -f /tmp/Interactive_Sql.Sql postgres
Are you sure to drop N
SELECT 1
psql:/tmp/Interactive_Sql.Sql:28: ERROR:  Database won't drop. Hence closing this session

bash-4.1$ ../bin/psql -U postgres -f /tmp/Interactive_Sql.Sql postgres
Are you sure to drop Y
SELECT 1
psql:/tmp/Interactive_Sql.Sql:28: NOTICE:  Database will be droped. Please make sure the connection of this databases get closed.
DO
DROP DATABASE

దినేష్ కుమార్ 

Dinesh Kumar