如何在psql脚本中使用变量 [英] how to use variables in a psql script

问题描述

这是我的psql脚本，它不起作用：

Here is my psql script, which does not work:

\set path '''c:\\server\\data\\''';
COPY paymentMethods (name,regexString) FROM :path+'paymentMethods.csv' WITH (FORMAT csv, HEADER true);
COPY priceLevels (name) FROM :path+'priceLevels.csv' WITH (FORMAT csv, HEADER false);

psql抱怨 +

如何更改它以使其起作用，同时只提及一次实际的路径字符串？

How can I change it so that it works, while having the actual path string mentioned just once?

推荐答案

首先，您尝试使用 + 运算符连接两个字符串，但用于串联的SQL运算符为 || ，有了这些信息，您可能会认为预期结果将是（不起作用）：

First of all, you are trying to concatenate two strings with + operator, but the SQL operator for concatenation is ||, with that information, you could think the expected result would be (won't work):

\set path '''c:\\server\\data\\'''
COPY paymentMethods (name,regexString) FROM :path || 'paymentMethods.csv' WITH (FORMAT csv, HEADER true);
COPY priceLevels (name) FROM :path || 'priceLevels.csv' WITH (FORMAT csv, HEADER false);

但是！ COPY 命令期望路径使用文字字符串，而不是表达式，因此您确实应该给出路径。请注意，它适用于 SELECT ， INSERT ， UPDATE 等。

But! The COPY command expect a literal string for the path, not an expression, so you really should give the path. Notice it would work for commands like SELECT, INSERT, UPDATE, etc.

有了这些信息，您只能使用 psql 变量，如Pavel指出的那样，并连接字符串放入 psql 的变量中。一个好的解决方案是使用 psql 的：'var'语法，该语法将变量作为字符串插入SQL表达式：

With that information, you can only use psql variables, as Pavel pointed, and concatenate the strings into a psql's variable. A good solution is to use the psql's :'var' syntax, that insert the variable as a string into the SQL expression:

\set path 'c:\\server\\data\\'
\set paymentMethodsPath :path 'paymentMethods.csv'
\set priceLevelsPath :path 'priceLevels.csv'
COPY paymentMethods (name,regexString) FROM :'paymentMethodsPath' WITH (FORMAT csv, HEADER true);
COPY priceLevels (name) FROM :'priceLevels' WITH (FORMAT csv, HEADER false);

将生成（将发送到PostgreSQL服务器）：

Which will generate (will send to the PostgreSQL's server):

COPY paymentMethods (name,regexString) FROM E'c:\\server\\data\\paymentMethods.csv' WITH (FORMAT csv, HEADER true);
COPY priceLevels (name) FROM E'c:\\server\\data\\priceLevels.csv' WITH (FORMAT csv, HEADER false);

此：'var'语法不会可以在所有 psql 版本上使用（我现在不记得介绍了哪个版本），但是对于旧版本，可以轻松地使用美元引号：

This :'var' syntax will not work on all psql versions (I don't recall now which one this was introduced), but for old versions you can easily use dollar-quoting:

\set path 'c:\\server\\data\\'
\set paymentMethodsPath :path 'paymentMethods.csv'
\set priceLevelsPath :path 'priceLevels.csv'
COPY paymentMethods (name,regexString) FROM $$:paymentMethodsPath$$ WITH (FORMAT csv, HEADER true);
COPY priceLevels (name) FROM $$:priceLevels$$ WITH (FORMAT csv, HEADER false);

或转义为单引号：

\set path 'c:\\server\\data\\'
\set paymentMethodsPath 'E''':path'paymentMethods.csv'''
\set priceLevelsPath 'E''':path'priceLevels.csv'''
COPY paymentMethods (name,regexString) FROM :paymentMethodsPath WITH (FORMAT csv, HEADER true);
COPY priceLevels (name) FROM :priceLevels WITH (FORMAT csv, HEADER false);

就是这样。

这篇关于如何在psql脚本中使用变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！