Skip to main content


Showing posts from November, 2013

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. Hen…