Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Let's consider the following example (from the start of a psql script):

\c :db_to_run_on

TRUNCATE the_most_important_table;
-- tried to avoid similarities to anything that exists out there

Now if it is run this by the command

psql [connection details] -v db_to_run_on=\'dev_database\'

then it just runs and the user is happy. But what if (s)he decides to specify -v db_to_run_on=production_database? (Let's assume that this can happen, just like people run rm -rf / # don't try this at home!!! ocassionally.) Hopefully there is a fresh backup of that table...

So the question arises: how to check the variables passed to a script and stop further processing based on their value?

share|improve this question
    
psql is quite limited as a batch tool, as it lacks control structures. I'd recommend writing scripts for this sort of thing in perl/python/whatever using the appropriate database driver. –  Craig Ringer Sep 19 '12 at 10:59
    
@CraigRinger it is, unfortunately. I have an idea now, but it needs a bit of honing yet. –  dezso Sep 19 '12 at 11:13
add comment

2 Answers 2

up vote 2 down vote accepted

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.

share|improve this answer
add comment

What I found works very well for me is to use a scripting language to generate a SQL file which I then pipe into psql, something like this:

#!/usr/bin/env ruby

raise "Not a good database name: #{ARGV.first.inspect}" unless ARGV.first =~ /^(dev|test)/

puts "\\timing off"
puts "set client_min_messages='warning';"
puts
puts "TRUNCATE the_most_important_table;"
puts "-- more commands"

Then, I call this from a driver script:

#!/bin/bash
/usr/bin/ruby generator ${1} | /usr/bin/psql --dbname=${1} --file=- --single-transaction

My driver script is usually a Rake file, but you get the idea.

share|improve this answer
    
Well, yes. I got it :) While I appreciate your input, this is exactly what I want to avoid - using an additional layer. –  dezso Sep 25 '12 at 7:13
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.