Thread: 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;