如何使用来自bash shell的psql命令执行多个查询?

I need to execute postgresql queries from command line using psql -c command. For every psql command, it opens a new tcp connection to connect to the database server and execute query which is a overhead for large number of queries.

我需要使用psql -c命令从命令行执行postgresql查询。对于每个psql命令，它打开一个新的tcp连接，连接到数据库服务器并执行查询，这是大量查询的开销。

Currently I can execute single query like this:

目前我可以执行这样的单个查询:

psql -U postgres -h <ip_addr> -c "SELECT * FROM xyz_table;"

When I tried to execute multiple queries as below, but only the last query got executed.

当我尝试执行多个查询时，如下所示，但只执行最后一个查询。

psql -U postgres -h <ip_addr> -c "SELECT * FROM xyz_table; SELECT * FROM abc_table;"

Can anyone help me and tell me the proper way to do it?

谁能帮助我并告诉我正确的方法吗?

4 个解决方案

#1

-c processes only one command. Without it however psql expects commands to be passed into standard input, e.g.:

-c只处理一个命令。但是，如果没有它，psql希望将命令传递到标准输入中，例如:

psql -U postgres -h <ip_addr> <database_name> << EOF
SELECT * FROM xyz_table;
SELECT * FROM abc_table;
EOF

Or by using echo and pipes.

或者使用回声和管道。

#2

Using echo and a pipe to fit it on a single line:

使用echo和管道将其安装在一条线上:

echo 'SELECT * FROM xyz_table; \n SELECT * FROM abc_table' | psql -U postgres 

#3

The --file parameter executes a file's content

文件参数执行文件的内容。

psql -U postgres -h <ip_addr> -f "my_file.psql"

All the output will be sent to standard output

所有的输出将被发送到标准输出

http://www.postgresql.org/docs/current/static/app-psql.html

http://www.postgresql.org/docs/current/static/app-psql.html

#4

at least from 9.6.2 this approach works as well:

至少从9.6.2开始，这种方法也有效:

psql -c "select now()" -c "select version()" -U postgres -h 127.0.0.1

现在psql - c”选择()”- c”选择版本()”- u postgres - h 127.0.0.1

now

2017-12-26 20:25:45.874935+01 (1 row)

2017-12-26 20:25:45.874935 + 01(1行)

version

PostgreSQL 9.6.2 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 5.3.1-14ubuntu2) 5.3.1 20160413, 64-bit (1 row)

关于x86_64-pc-linux-gnu的PostgreSQL 9.6.2，由gcc (Ubuntu 5.3.1-14ubuntu2)编译