Passing parameters into an in-line psql invocation

On 06/02/11 9:58 AM, 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.

on the psql command line,
-v name=value
or
--set name=value

then in your script, use :name if you want to use "value" as a sql
identifier and (in 9.x), you can use :'value' if you want to use
'value' as a string literal.

--
john r pierce N 37, W 123
santa cruz ca mid-left coast

Gauthier, Dave wrote:

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.

You may want to use the --set or --variable options of psql and then
reference the variable name in thesqlscript.

So the psql becomes:
psql --set 'var=foo' -c '\i thesqlscript'

and then in thesqlscript:
update table set column = :var;

HTH

Bosco.

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;