PostgreSQL - 批处理 + 脚本 + 变量

PostgreSQL - batch + script + variable

提问by damien

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.

我有一个批处理文件连接到我的 PostgreSQL 服务器，然后打开一个 sql 脚本。一切都按预期工作。我的问题是如何将变量（如果可能）从一个传递到另一个。

Here is my batch file:

这是我的批处理文件：

set PGPASSWORD=xxxx
cls
@echo off
C:\Progra~1\PostgreSQL.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.

这很好用，现在的问题是我如何传递一个带有日期的变量data_analisi？就像在批处理文件中，“请输入日期”，然后将值传递给 sql 脚本。

采纳答案by Erwin Brandstetter

You could create a functionout of your your SQL script like this:

你可以像这样从你的 SQL 脚本中创建一个函数：

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 = 
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 SQLfor this kind of simple SQL batch.

您可以将语言 SQL用于这种简单的 SQL 批处理。

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 totalproteinas 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.ptyou would set the column to NULL, if anythinggoes wrong in the process (datenot found, wrong date, idnot found ...). The way I rewrote the UPDATE, it only happens if matching data are found. I assume that is what you actually want.

• 要将数据临时读入空表，请使用临时表。节省了大量的光盘写入，而且速度要快得多。

• 为了简化过程，我使用您现有的表totalprotein作为创建（空）临时表的模板。

• 如果要删除表的所有行，请使用 TRUNCATE 而不是 DELETE FROM。快多了。在这种特殊情况下，您两者都不需要。临时表会自动删除。见函数中的注释。

• 如果在此过程中出现任何问题（未找到、错误、未找到...），您更新的方式anagrafica.pt会将列设置为。我重写 UPDATE 的方式，只有在找到匹配的数据时才会发生。我认为这就是你真正想要的。NULLdatedateid

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 IDENTmethod in pg_haba.conf):

然后在您的 shell 脚本中要求用户输入并使用日期作为参数调用该函数。这就是它在 Linux shell 中的工作方式（作为用户 postgres，使用无密码访问（使用 中的IDENT方法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')"

-cmakes psql execute a singe SQL command and then exits. I wrote a lot more on psql and its command line optionsyesterday in a somewhat related answer.

-c使 psql 执行单个 SQL 命令然后退出。我昨天在一个有点相关的答案中写了更多关于psql 及其命令行选项的内容。

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

相应的Windows 批处理文件的创建仍然是您的练习。

---

---

Call under Windows

Windows下调用

The error message tells you:

错误消息告诉您：

Function tpimport(unknown) does not exist

函数 tpimport(unknown) 不存在

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.

注意小写字母：tpimport. 我怀疑您使用混合大小写字母来创建该函数。所以现在你每次使用时都必须用双引号将函数名括起来。

Try this one (edited quotes!):

试试这个（编辑引号！）：

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

Note how I use singe and double quoteshere. I guessthis could work under windows. See here.

请注意我在这里如何使用单引号和双引号。我想这可以在 Windows 下工作。见这里。

You made it hard for yourself when you chose to use mixed case identifiersin 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 neverdo 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.

当您选择在 PostgreSQL 中使用混合大小写标识符时，您为自己感到难过——这是一种我从不厌倦警告的愚蠢行为。现在你"TPImport"每次使用函数名时都必须用双引号引起来。虽然完全合法，但我永远不会这样做。我使用小写字母作为标识符。总是。这样我就不会混淆小写/大写，也不必使用双引号。

The ultimate fixwould 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.

在此处阅读有关标识符的基础知识。
另外，考虑升级到更新版本的 PostgreSQL 8.3 现在有点生疏了。

回答by A.H.

psqlsupports textual replacement variables. Within psqlthey can be set using \setand used using :varname.

psql支持文本替换变量。在psql它们中可以设置 using\set和 using using 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 \setand select.

唯一的问题是这些文本替换总是需要对引用进行一些修改，如第一个\set和select.

回答by Harlin Acero

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:

在 Postgres 中创建函数后，您必须在您的 Postgres 版本的 bin 目录中创建一个 .bat 文件，例如 .bat 文件C:\Program Files\PostgreSQL\9.3\bin。在这里你写：

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