033.PGSQL-psql-登录时执行sql命令和sql文件，传递参数

-c  执行sql语句

-A  返回数据非对齐模式

-t 返回数据本身

 

-f 执行sql文件

-q 不显示输出信息

 

元命令设置变量

\set  v_id  value  设置参数

id =:v_id

psql  传参变量到sql脚本   -v

psql -h localhost -p 5432 mydb pguser -v v_id = 1   -f /var/lib/pgsql/13/test1.sql

 

[root@s101 /home/centos]#psql -h localhost -p 5432 mydb pguser -c "select * from myschema.o_ls_test;"
Password for user pguser:
 rid | name |          timeflag
-----+------+----------------------------
   1 | aa   | 2021-07-03 20:12:15.004507
   3 | cc   | 2021-07-03 20:12:45.117668
(2 rows)

[root@s101 /home/centos]#psql -h localhost -p 5432 mydb pguser -A -c "select * from myschema.o_ls_test;"
Password for user pguser:
rid|name|timeflag
1|aa|2021-07-03 20:12:15.004507
3|cc|2021-07-03 20:12:45.117668
(2 rows)
[root@s101 /home/centos]#psql -h localhost -p 5432 mydb pguser -At -c "select * from myschema.o_ls_test;"
Password for user pguser:
1|aa|2021-07-03 20:12:15.004507
3|cc|2021-07-03 20:12:45.117668
[root@s101 /home/centos]#psql -h localhost -p 5432 mydb pguser -At -c "select name from myschema.o_ls_test limit 1;"
Password for user pguser:
aa

 

 

 执行sql文件

[root@s101 /home/centos]#psql -h localhost -p 5432 mydb pguser -f /var/lib/pgsql/13/test.sql
Password for user pguser:
INSERT 0 1

 

传递参数

mydb=# \set v_rid 5
mydb=# select * from myschema.o_ls_test where rid =:v_rid;
 rid | name |          timeflag
-----+------+----------------------------
   5 | bb   | 2021-07-03 20:47:43.066873
(1 row)

test1.sql

select * from  myschema.o_ls_test  where rid=:v_rid;

执行脚本传递参数

[root@s101 /home/centos]#psql -h localhost -p 5432 mydb pguser -v v_rid=5  -f /var/lib/pgsql/13/test1.sql
Password for user pguser:
 rid | name |          timeflag
-----+------+----------------------------
   5 | bb   | 2021-07-03 20:47:43.066873
(1 row)