Bash 脚本和 PostgreSQL：如何访问从 SELECT 语句返回的列值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20328191/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Bash script & PostgreSQL: How to access column values returned from SELECT statement
提问by Robbie Torrens
I have a database in PostgreSQL called customers, customers has a table called CustomerInfo. CustomerInfo contains 3 columns ID, Name and address. I would like to write a bash script to get the information from the CustomerInfo table but i am not sure how to access the individual rows once i have the results of the query. Here is the script i have written:
我在 PostgreSQL 中有一个名为客户的数据库，客户有一个名为 CustomerInfo 的表。CustomerInfo 包含 3 列 ID、名称和地址。我想编写一个 bash 脚本来从 CustomerInfo 表中获取信息，但是一旦获得查询结果，我不确定如何访问各个行。这是我写的脚本：
#!/bin/bash
results=`psql -d customers -c "select * from CustomerInfo where name = 'Dave'"`
echo $results['name']
The query runs correctly and returns the correct results but the echo command will just print everything in results. I know this is not the correct way of doing this, does anyone know of a way to get the query results as an array, or would i just have to write my own function for parsing the results?
查询正确运行并返回正确的结果，但 echo 命令只会打印结果中的所有内容。我知道这不是正确的方法，有没有人知道将查询结果作为数组获取的方法，或者我是否只需要编写自己的函数来解析结果？
Thanks!
谢谢！
回答by bckim
You can store your results into an array and loop through it using a while loop.
您可以将结果存储到一个数组中并使用 while 循环遍历它。
psql -d customers -c "select * from CustomerInfo where name = 'Dave'"
| while read -a Record ; do
# ${Record[0]} is your ID field
# ${Record[1]} is your Name field
# ${Record[2]} is your address field
done
回答by yokeho
I had success using the
我成功地使用了
psql | while read do
approach:
方法：
$PG_HOME/bin/psql \
-h 127.0.0.1 \
-U $DB_USER \
-c 'SELECT recipient_email, name FROM report_recipients' \
--set ON_ERROR_STOP=on \
--no-align \
--quiet \
--no-password \
--tuples-only \
--field-separator ' ' \
--pset footer=off \
--dbname=$DATABASE \
| while read EMAIL_ADDRESS NAME ; do
echo "$SCRIPT: addr=$EMAIL_ADDRESS name=$NAME"
done
There is a great resource for this and similar things at: manniwood.com's PostgreSQL and bash Stuff page
有一个很好的资源用于这个和类似的东西：manniwood.com 的 PostgreSQL 和 bash Stuff 页面
回答by sat
You can't. You have to write your own function for paring the query results. Backtick( ` ) will execute the command and returns the output. In your case, results will have output of your query.
你不能。您必须编写自己的函数来配对查询结果。Backtick( ` ) 将执行命令并返回输出。在您的情况下，结果将输出您的查询。
回答by Chris Travers
If I were tasked with doing this (really, Perl is better, take the advice of @daniel-verite), here is what I would do.
如果我的任务是做这件事（真的，Perl 更好，听从@daniel-verite 的建议），这就是我会做的。
- Get a list of column names by piping the initial results through
head - Create a function which parses the rest using awk into a useful format.
- sed or awk to extract the relevant information from a row.
- 通过管道传递初始结果来获取列名列表
head - 创建一个函数，该函数使用 awk 将其余部分解析为有用的格式。
- sed 或 awk 从一行中提取相关信息。
Now, the above assumes no embedded newlines (which would complicate things)
现在，上面假设没有嵌入的换行符（这会使事情复杂化）
This amount of effort is anything but trivial. You really are better off learning Perl for a task like this.
这样的努力绝不是微不足道的。对于这样的任务，你真的最好学习 Perl。
