Обсуждение: Passing parameters into an in-line psql invocation
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 !
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;