Listing 7. Create a New Employee in People and Salaries

#!/usr/bin/perl -wT
# insert-new-person.pl

use strict;
use DBI;

# Get parameters
my ($new_name, $new_email, $monthly_gross) = @ARGV;
die qw { "You need to specify (a) name (b)
         e-mail address, (c) monthly gross"
       } unless (@ARGV == 3);

# Connect to the PostgreSQL server with DBD::Pg
my $host = 'ahad-haam';
my $user = 'reuven';
my $password = '';
my $dsn = "DBI:Pg:dbname=test;host=$host;";
my $dbh =
    DBI->connect($dsn, $user, $password,
       {RaiseError => 1, AutoCommit => 0});

# Insert the new employee into the People table
my $sql = "INSERT INTO People ";
$sql .= " (name, email) ";
$sql .= "VALUES ";
$sql .= " (?, ?) ";

$dbh->do($sql, undef, $new_name, $new_email);

# Get the person_id that we just inserted
$sql = "SELECT currval('people_person_id_seq')";

my $person_id = $dbh->selectrow_array($sql);

# Now insert a row into the Salaries table, using $person_id
$sql = "INSERT INTO Salaries ";
$sql .= " (person_id, monthly_gross, as_of, ";
$sql .= " approved_by, notes) ";
$sql .= "VALUES ";
$sql .= " (?, ?, NOW(), ?, ?) ";

# We will assume here that the boss has a person_id
# of 1 -- but
# hard-wiring this sort of information is a bad idea
# in practice.

$dbh->do($sql, undef, $person_id, $monthly_gross,
         1, "Testing insert");

# If we do not commit this transaction, PostgreSQL
# will behave as if
# we rolled it back and it never happened!
$dbh->commit;

$dbh->disconnect;