Re: Passing parameters into an in-line psql invocation

On Thursday 2. June 2011 18.58.23 Gauthier, Dave wrote:
> Hi:
>
> I'd like to pass a parameter into an inline psql call that itself calls an
> sql script, something like...
>
> psql mydb -c "\i thesqlscript foo"
>
> Where"foo" is the value I want to pass in.
>
> Just as good would be the ability to sniff out an environment variable from
> within the sql script ("thesqlscript" in the example above). In perl, I
> would use $ENV{VARNAME}. Is there something like that in Postgres SQL?
>
> V8.3.4 on Linux (upgrading to v9 very soon).
>
> Thanks for any ideas !

Personally I prefer to write a small wrapper in Perl for interaction with
Postgres from the command line. Here's a boilerplate:

#! /usr/bin/perl

use strict;
use DBI;

my $val = shift;
if ((!$val) || !($val =~ /^\d+$/)) {
print "Bad or missing parameter $val\n";
exit;
}
my $dbh = DBI->connect("dbi:Pg:dbname=mydb", '', '',
{AutoCommit => 1}) or die $DBI::errstr;
my $sth = $dbh->prepare("SELECT foo(?)");
while (my $text = <STDIN>) {
chomp($text);
$sth->execute($val);
my $retval = $sth->fetch()->[0];
if ($retval < 0) {
$retval = abs($retval);
print "Duplicate of $retval, not added.\n";
}
else {
print "$retval added.\n";
}
}
$sth->finish;
$dbh->disconnect;