PostgreSQL Oracle 兼容性之 - psql prompt like Oracle SQL*Plus
背景
Oracle的SQL*Plus客户端支持使用promote输入变量值,然后在脚本的其他位置使用该变量值。
例如大量的dbms脚本使用了这个用法, 如statspack输入起始值。
https://docs.oracle.com/cd/B19306_01/server.102/b14357/ch12032.htm
在PostgreSQL中,psql客户端也提供了类似的用法,例如:
postgres=# create table test(id int, info text);
CREATE TABLE
postgres=# insert into test select generate_series(1,100),'test';
INSERT 0 100
提示输入ID的值,返回该ID对应的test的行。
vi test.sql
\prompt "please enter a id: " id
select * from test where id=:id;
-> psql -h 127.0.0.1 -p 1922 -f ./test.sql
"please enter a id: "1
id | info
----+------
1 | test
(1 row)
在psql命令行中执行
postgres=# \ir test.sql
"please enter a id: "1
id | info
----+------
1 | test
(1 row)
其中
\i FILE execute commands from file
\ir FILE as \i, but relative to location of current script
详细的psql用法,可以参考帮助文档
man psql
\prompt [ text ] name
Prompts the user to supply text, which is assigned to the variable name. An optional prompt string, text, can be specified. (For multiword prompts, surround the text with single quotes.)
By default, \prompt uses the terminal for input and output. However, if the -f command line switch was used, \prompt uses standard input and standard output.