PostgreSQL - batch + script + variable

Question

I am not a programmer, I am struggling a bit with this.

I have a batch file connecting to my PostgreSQL server, and then open a sql script. Everything works as expected. My question is how to pass a variable (if possible) from one to the other.

Here is my batch file:

set PGPASSWORD=xxxx
cls
@echo off
C:\Progra~1\PostgreSQL\8.3\bin\psql -d Total -h localhost -p 5432 -U postgres -f C:\TotalProteinImport.sql

And here's the script:

copy totalprotein from 'c:/TP.csv' DELIMITERS ',' CSV HEADER;

update anagrafica
set pt=(select totalprotein.resultvalue from totalprotein where totalprotein.accessionnbr=anagrafica.id)
where data_analisi = '12/23/2011';

delete from totalprotein;

This is working great, now the question is how could I pass a variable that would carry the date for data_analisi? Like in the batch file, "Please enter date", and then the value is passed to the sql script.

Answer 1

You could create a function out of your your SQL script like this:

CREATE OR REPLACE FUNCTION f_myfunc(date)
  RETURNS void AS
$BODY$

CREATE TEMP TABLE t_tmp ON COMMIT DROP AS
SELECT * FROM totalprotein LIMIT 0; -- copy table-structure from table

COPY t_tmp FROM 'c:/TP.csv' DELIMITERS ',' CSV HEADER;

UPDATE anagrafica a
SET    pt = t.resultvalue
FROM   t_tmp t
WHERE  a.data_analisi = $1
AND    t.accessionnbr = a.id;

-- Temp table is dropped automatically at end of session
-- In this case (ON COMMIT DROP) after the transaction

$BODY$
  LANGUAGE sql;

You can use language SQL for this kind of simple SQL batch.

As you can see I have made a couple of modifications to your script that should make it faster, cleaner and safer.

Major points

• For reading data into an empty table temporarily, use a temporary table. Saves a lot of disc writes and is much faster.

• To simplify the process I use your existing table totalprotein as template for the creation of the (empty) temp table.

• If you want to delete all rows of a table use TRUNCATE instead of DELETE FROM. Much faster. In this particular case, you need neither. The temporary table is dropped automatically. See comments in function.

• The way you updated anagrafica.pt you would set the column to NULL, if anything goes wrong in the process (date not found, wrong date, id not found ...). The way I rewrote the UPDATE, it only happens if matching data are found. I assume that is what you actually want.

Then ask for user input in your shell script and call the function with the date as parameter. That's how it could work in a Linux shell (as user postgres, with password-less access (using IDENT method in pg_haba.conf):

#! /bin/sh

# Ask for date. 'YYYY-MM-DD' = ISO date-format, valid with any postgres locale.
echo -n "Enter date in the form YYYY-MM-DD and press [ENTER]: "
read date

# check validity of $date ...
psql db -p5432 -c "SELECT f_myfunc('$date')"

-c makes psql execute a singe SQL command and then exits. I wrote a lot more on psql and its command line options yesterday in a somewhat related answer.

The creation of the according Windows batch file remains as exercise for you.

---

Call under Windows

The error message tells you:

Function tpimport(unknown) does not exist

Note the lower case letters: tpimport. I suspect you used mixe case letters to create the function. So now you have to enclose the function name in double quotes every time you use it.

Try this one (edited quotes!):

C:\Progra~1\PostgreSQL\8.3\bin\psql -d Total -h localhost -p 5432 -U postgres
                                    -c "SELECT ""TPImport""('%dateimport%')"

Note how I use singe and double quotes here. I guess this could work under windows. See here.

You made it hard for yourself when you chose to use mixed case identifiers in PostgreSQL - a folly which I never tire of warning against. Now you have to double quote the function name "TPImport" every time you use it. While perfectly legit, I would never do that. I use lower case letters for identifiers. Always. This way I never mix up lower / upper case and I never have to use double quotes.

The ultimate fix would be to recreate the function with a lower case name (just leave away the double quotes and it will be folded to lower case automatically). Then the function name will just work without any quoting.

Read the basics about identifiers here.
Also, consider upgrading to a more recent version of PostgreSQL 8.3 is a bit rusty by now.

Answer 2

psql supports textual replacement variables. Within psql they can be set using \set and used using :varname.

\set xyz 'abcdef'
select :'xyz';
 ?column? 
----------
abcdef

These variables can be set using command line arguments also:

psql -v xyz=value

The only problem is that these textual replacements always need some fiddling with quoting as shown by the first \set and select.

Answer 3

After creating the function in Postgres, you must create a .bat file in the bin directory of your Postgres version, for example C:\Program Files\PostgreSQL\9.3\bin. Here you write:

@echo off
cd C:\Program Files\PostgreSQL\9.3\bin
psql -p 5432 -h localhost -d myDataBase -U postgres -c "select * from myFunction()"