PostgreSQL 使用psql插入参数到sql脚本文件中

1. 数据

postgres=# select * from tb1;
 id  | name 
-----+------
   2 | aa
   3 | aa
   4 | aa
   7 | aa
   8 | aa
   9 | aa
  10 | aa
   1 | cc
   5 | cc
   6 | cc
 100 | vv
(11 rows)

2. 准备sql脚本文件

vi test.sql

写入 select * from tb1 where id=:id; (:+变量名)

保存。

3. 使用psql命令执行sql脚本

[postgres@localhost ~]$ psql postgres postgres -v id=1 -f /home/postgres/test.sql 
 id | name 
----+------
  1 | cc
(1 row)

[postgres@localhost ~]$ psql postgres postgres -v id=2 -f /home/postgres/test.sql 
 id | name 
----+------
  2 | aa
(1 row)

4. psql命令使用-c 调用\i命令

[postgres@localhost ~]$ psql postgres postgres -v id=2 -c '\i /home/postgres/test.sql '
 id | name 
----+------
  2 | aa
(1 row)

5. psql使用文档

[postgres@localhost ~]$ psql --help
psql is the PostgreSQL interactive terminal.

Usage:
  psql [OPTION]... [DBNAME [USERNAME]]

General options:
  -c, --command=COMMAND    run only single command (SQL or internal) and exit
  -d, --dbname=DBNAME      database name to connect to (default: "postgres")
  -f, --file=FILENAME      execute commands from file, then exit
  -l, --list               list available databases, then exit
  -v, --set=, --variable=NAME=VALUE
                           set psql variable NAME to VALUE
  -V, --version            output version information, then exit
  -X, --no-psqlrc          do not read startup file (~/.psqlrc)
  -1 ("one"), --single-transaction
                           execute as a single transaction (if non-interactive)
  -?, --help               show this help, then exit

Input and output options:
  -a, --echo-all           echo all input from script
  -e, --echo-queries       echo commands sent to server
  -E, --echo-hidden        display queries that internal commands generate
  -L, --log-file=FILENAME  send session log to file
  -n, --no-readline        disable enhanced command line editing (readline)
  -o, --output=FILENAME    send query results to file (or |pipe)
  -q, --quiet              run quietly (no messages, only query output)
  -s, --single-step        single-step mode (confirm each query)
  -S, --single-line        single-line mode (end of line terminates SQL command)

Output format options:
  -A, --no-align           unaligned table output mode
  -F, --field-separator=STRING
                           set field separator (default: "|")
  -H, --html               HTML table output mode
  -P, --pset=VAR[=ARG]     set printing option VAR to ARG (see \pset command)
  -R, --record-separator=STRING
                           set record separator (default: newline)
  -t, --tuples-only        print rows only
  -T, --table-attr=TEXT    set HTML table tag attributes (e.g., width, border)
  -x, --expanded           turn on expanded table output
  -z, --field-separator-zero
                           set field separator to zero byte
  -0, --record-separator-zero
                           set record separator to zero byte

Connection options:
  -h, --host=HOSTNAME      database server host or socket directory (default: "/usr/local/postgres-9.3.5/data")
  -p, --port=PORT          database server port (default: "5432")
  -U, --username=USERNAME  database user name (default: "postgres")
  -w, --no-password        never prompt for password
  -W, --password           force password prompt (should happen automatically)

For more information, type "\?" (for internal commands) or "\help" (for SQL
commands) from within psql, or consult the psql section in the PostgreSQL
documentation.

参考：[http://francs3.blog.163.com/blog/static/40576727201342834353302/](http://francs3.blog.163.com/blog/static/40576727201342834353302/)