[GENERAL] Passing parameters into an in-line psql invocation

[Gauthier, Dave]

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

Wherefoo 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 !

Re: [GENERAL] Passing parameters into an in-line psql invocation

[John R Pierce]

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

Wherefoo 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 pierceN 37, W 123
santa cruz ca mid-left coast


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Passing parameters into an in-line psql invocation

[Bosco Rama]

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
 
 Wherefoo 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.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Passing parameters into an in-line psql invocation

[Leif Biberg Kristensen]

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
 
 Wherefoo 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;

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general