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/