作者
digoal
日期
2019-07-18
标签
PostgreSQL , Oracle , 兼容性 , 变量 , & , set , variable
背景
https://blog.csdn.net/haiross/article/details/15340489
Oracle sqlplus 的变量使用：
```
非PL/SQL变量5.1、替换变量（仅用于SQL Plus或者用于原理和SQL Plus相同的开发工具）：
临时存储值
利用它可以达到创建通用脚本的目的
利用它可以达到和用户交互，故在SQL *Plus中又称交互式命令
替换变量的格式式在变量名称前加一个&，以便在运行SQL命令时提示用户输入替换数据，然后按输入数据运行SQL命令
语法：
（1）& ：“&变量名”eg：&name;
生命周期：单次引用中，不需要声明，如果替换字符或日期类型，最好用单引号扩起
使用范围：where、order by、列表达式、表名、整个SELECT 语句中
（2）&& ：“&&变量名”eg：&&name;
生命周期：整个会话（session连接）,不需要声明
（3）define ：“define 变量名=变量值”eg：DEFINE a = clark;
生命周期：整个会话，预先声明，使用时用&引用声明的变量
define variable=用户创建的CHAR类型的值：define 变量名=值；
define column_name(变量名)：查看变量命令。
undefine 变量名：清除变量
define：查看在当前会话中所有的替换变量和它们的值
```
psql是PostgreSQL的命令行客户端，功能非常强大，同样支持变量。
```
Meta-Commands
Anything you enter in psql that begins with an unquoted backslash is a psql meta-command that is processed by psql itself.
These commands make psql more useful for administration or scripting. Meta-commands are often called slash or backslash commands.
The format of a psql command is the backslash, followed immediately by a command verb, then any arguments.
The arguments are separated from the command verb and each other by any number of whitespace characters.
To include whitespace in an argument you can quote it with single quotes. To include a single quote in an argument, write two single quotes within single-quoted text. Anything contained in single quotes is furthermore subject to C-like substitutions for \n (new line), \t (tab), \b (backspace), \r (carriage return), \f (form feed), \digits (octal), and \xdigits (hexadecimal). A backslash preceding any other character within single-quoted text quotes that single character, whatever it is.
If an unquoted colon (:) followed by a psql variable name appears within an argument, it is replaced by the variable's value, as described in SQL Interpolation. The forms :'variable_name' and :"variable_name" described there work as well. The :{?variable_name} syntax allows testing whether a variable is defined. It is substituted by TRUE or FALSE. Escaping the colon with a backslash protects it from substitution.
Within an argument, text that is enclosed in backquotes (`) is taken as a command line that is passed to the shell.
The output of the command (with any trailing newline removed) replaces the backquoted text.
Within the text enclosed in backquotes, no special quoting or other processing occurs, except that appearances of :variable_name where variable_name is a psql variable name are replaced by the variable's value. Also, appearances of :'variable_name' are replaced by the variable's value suitably quoted to become a single shell command argument. (The latter form is almost always preferable, unless you are very sure of what is in the variable.)
Because carriage return and line feed characters cannot be safely quoted on all platforms, the :'variable_name' form prints an error message and does not substitute the variable value when such characters appear in the value.
Some commands take an SQL identifier (such as a table name) as argument.
These arguments follow the syntax rules of SQL: Unquoted letters are forced to lowercase, while double quotes (") protect letters from case conversion and allow incorporation of whitespace into the identifier.
Within double quotes, paired double quotes reduce to a single double quote in the resulting name.
For example, FOO"BAR"BAZ is interpreted as fooBARbaz, and "A weird"" name" becomes A weird" name.
Parsing for arguments stops at the end of the line, or when another unquoted backslash is found.
An unquoted backslash is taken as the beginning of a new meta-command.
The special sequence \ (two backslashes) marks the end of arguments and continues parsing SQL commands, if any.
That way SQL and psql commands can be freely mixed on a line.
But in any case, the arguments of a meta-command cannot continue beyond the end of the line.
Many of the meta-commands act on the current query buffer.
This is simply a buffer holding whatever SQL command text has been typed but not yet sent to the server for execution.
This will include previous input lines as well as any text appearing before the meta-command on the same line.
```
会话级变量用法1
1、通过psql传参数，或在psql shell中使用\set设置变量。
```
pg12@pg11-test-> psql --set=var1=t1
psql (12beta2)
Type "help" for help.
postgres=# explain select * from :var1 limit 10;
QUERY PLAN
Limit (cost=0.00..0.16 rows=10 width=17)
-> Seq Scan on t1 (cost=0.00..32739.00 rows=2000000 width=17)
(2 rows)
```
```
postgres=# \set var2 tbl1
postgres=# explain select * from :var2 limit 1;
QUERY PLAN
Limit (cost=0.00..0.02 rows=1 width=9)
-> Seq Scan on tbl1 (cost=0.00..154053.60 rows=9999860 width=9)
(2 rows)
```
2、通过冒号引用变量（替换为变量值）
postgres=# \echo :var1
t1
postgres=# \echo :'var1'
't1'
postgres=# \echo :"var1"
"t1"
postgres=# \set var3 "hello world"
postgres=# \echo :var3
"hello world"
postgres=# \echo :'var3'
'"hello world"'
postgres=# \echo :"var3"
"""hello world"""
postgres=# \set var3 "hello ""world"
postgres=# \echo :var3
"hello ""world"
3、通过冒号判断变量是否定义
postgres=# \echo :{?var1}
TRUE
postgres=# \echo :{?var2}
TRUE
postgres=# \echo :{?var3}
TRUE
postgres=# \echo :{?var4}
FALSE
4、通过echo查看变量值
5、通过unset重置变量
postgres=# \unset var1
postgres=# \echo :{?var1}
FALSE
会话级变量2
更多psql的用法，参考手册。
```
postgres=# \d t1
Table "public.t1"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
c1 | integer | | not null |
c2 | integer | | |
c3 | integer | | |
info | text | | |
Indexes:
"t1_pkey" PRIMARY KEY, btree (c1)
"idx_t1_1" btree (c2)
"idx_t1_2" btree (c3)
postgres=# \set col1 c1
postgres=# \set v digoal
postgres=# explain select :col1 from t1 where info=:'v';
QUERY PLAN
Seq Scan on t1 (cost=0.00..37739.00 rows=1 width=4)
Filter: (info = 'digoal'::text)
(2 rows)
```
参考
《PostgreSQL 12 preview - pgbench 压测工具编程能力增强 - gset 支持SQL结果返回并存入变量使用》
man psql
https://www.postgresql.org/docs/devel/app-psql.html
《快速入门PostgreSQL应用开发与管理 - 1 如何搭建一套学习、开发PostgreSQL的环境》
《PostgreSQL 10 on ECS 实施 流复制备库镜像+自动快照备份+自动备份验证+自动清理备份与归档 - 珍藏级》
《PostgreSQL 对象权限如何在元数据中获取 - 权限解读、定制化导出权限》
https://blog.csdn.net/haiross/article/details/15340489
PostgreSQL 许愿链接
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
9.9元购买3个月阿里云RDS PostgreSQL实例
PostgreSQL 解决方案集合
德哥 / digoal's github - 公益是一辈子的事.
