10

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?

13

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.

  • \set ON_ERROR_STOP on - nice! – msciwoj Apr 7 '17 at 14:13
5

PostgreSQL 10

PostgreSQL 10 brings conditionals to psql. This is no longer an issue.

\if :db_to_run_on = 'dev_database'
  TRUNCATE the_most_important_table;
\endif

I guess you could also use DO..

\if :db_to_run_on != 'dev_database'
do $$
  BEGIN
    RAISE 'Meaningful error message here';
  END;
$$ LANGUAGE plpgsql;
\endif
  • ...no longer an issue if you happen to be running PostgreSQL 10. – Steve Bennett Sep 20 '17 at 4:25
  • 1
    @SteveBennett pretty clear about that. But I think it's not entirely true. You only need the psql on version 10, not the server backend. – Evan Carroll Sep 20 '17 at 4:27
  • Oh, that's interesting. But yeah, old versions can stay around a looooong time. – Steve Bennett Sep 20 '17 at 6:07
  • You can also \set ON_ERROR_STOP 1 and then \if yes \endif to require psql version 10 or higher. :) (Earlier versions will complain about \if being invalid, and then quit.) – Wildcard Mar 23 '18 at 1:34
1

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.

  • 2
    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
1

A more concise version of dezso's answer:

CREATE OR REPLACE FUNCTION pg_temp.err(msg varchar) RETURNS boolean     
AS $$ BEGIN RAISE '%',msg; END; $$ LANGUAGE plpgsql;

You can then call this like:

\set ON_ERROR_STOP on

SELECT CASE WHEN (
  SELECT COUNT(*) FROM mytable
) > 0 THEN pg_temp.err('Already loaded') END;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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