在 PostgreSQL 查询中声明一个变量
- 在 PostgreSQL 查询中声明一个变量
-
在 PostgreSQL 查询中使用
WITH子句来声明一个变量 - 使用 PostgreSQL 过程语言在 PostgreSQL 查询中声明变量
- 使用动态配置设置在 PostgreSQL 查询中声明变量
变量是程序中用于存储使用特定数据类型声明的数据的临时内存分配。一旦执行完成，变量上的数据将被丢弃，并且需要持久存储以在需要时检索数据。
在本教程中，我们将学习在 PostgreSQL 中创建变量并使用该变量对数据库执行查询的不同方法。
在 PostgreSQL 查询中声明一个变量
使用以下命令登录 PostgreSQL 服务器。
david@david-HP-ProBook-6470b:~$ psql -U postgres
Password for user postgres:
在打开的提示中输入你的密码，然后按键盘上的 Enter 按钮。
psql (14.2 (Ubuntu 14.2-1.pgdg18.04+1))
Type "help" for help.
创建一个名为 variable_db 的数据库，我们将使用它来创建一个用于测试目的的表。
postgres=# create database variable_db;
CREATE DATABASE
使用以下命令连接到我们刚刚创建的数据库。
postgres=# \c variable_db;
You are now connected to database "variable_db" as user "postgres".
连接到数据库，我们刚刚创建确保我们执行的任何数据定义语言或操作语言只影响 variable_db 数据库。
创建一个名为 vehicle 的表，其中包含字段 id、vehicle_name、vehicle_type、vehicle_model 和 vehicle_price。
vehicle_name、vehicle_type 和 vehicle_model 字段是类型 string，而 id 和 vehicle_price 字段是类型 integer。
variable_db=# create table vehicle(vehicle_name varchar(30), vehicle_type varchar(30), vehicle_model varchar(30), vehicle_price integer, id SERIAL UNIQUE NOT NULL, PRIMARY KEY(id));
CREATE TABLE
将三个记录插入表中，为你添加的每个车辆实例提供名称、类型、型号和价格。
将下面的 SQL 命令复制并粘贴到你的终端上，然后按键盘上的 Enter。
variable_db=# insert into vehicle(vehicle_name, vehicle_type, vehicle_model, vehicle_price) values('Jaguar Range Rover Evoque','4WD','2019 Model',7000000);
INSERT 0 1
variable_db=# insert into vehicle(vehicle_name, vehicle_type, vehicle_model, vehicle_price) values('Porsche Cayenne','4WD','2019 Model',7000000);
INSERT 0 1
variable_db=# insert into vehicle(vehicle_name, vehicle_type, vehicle_model, vehicle_price) values('BMW 1 Series','4WD','2019 Model',7000000);
INSERT 0 1
我们将使用上面创建的表中的数据来学习创建变量并使用变量执行查询的不同方法。
在 PostgreSQL 查询中使用 WITH 子句来声明一个变量
将以下代码复制并粘贴到你的终端上，然后按键盘上的 Enter 按钮。
variable_db=# WITH prices AS(SELECT 7000000 as carprices)
SELECT vehicle_name FROM vehicle, prices WHERE vehicle_price = prices.carprices;
WITH 子句允许我们创建临时表并添加一个 select 查询与别名结合以创建列的临时变量。
别名使用关键字 AS 后跟一个包含描述性名称的变量名，以避免在执行过程中混淆。
临时表 prices 包含一个临时变量，其中包含值 7000000；我们使用临时变量来查找所有表格中哪些车辆具有该价格。以下是查询的结果。
vehicle_name
---------------------------
Jaguar Range Rover Evoque
Porsche Cayenne
BMW 1 Series
(3 rows)
使用 PostgreSQL 过程语言在 PostgreSQL 查询中声明变量
要创建过程语言，请创建一个名为 procedure.sql 的文件并将以下过程写入该文件。你可以将代码复制并粘贴到文件中。
DO $$
DECLARE price integer;
BEGIN
SELECT 7000000 INTO price;
DROP TABLE IF EXISTS expvehicles;
CREATE TABLE expvehicles AS
SELECT vehicle_name FROM vehicle WHERE vehicle_price = price;
END $$;
SELECT * FROM expvehicles;
该过程创建了一个名为 price 的变量，它的值是 70000000。我们将使用此变量来过滤以该值定价的车辆。
查询结果将存储在名为 expvehicles 的临时表中。该过程的最后一条语句执行一个 select 查询，返回所有价格为 7000000 的车辆。
将下面的命令复制并粘贴到你的终端中以执行此文件，然后按键盘上的 Enter 按钮。
variable_db=# \i /home/david/Documents/work/upwork/jhinku-tutorials/procedure.sql
DO
上述命令返回一个包含车辆名称的表，但该表不是临时表，执行后数据会丢失。
vehicle_name
---------------------------
Jaguar Range Rover Evoque
Porsche Cayenne
BMW 1 Series
(3 rows)
使用动态配置设置在 PostgreSQL 查询中声明变量
我们使用 set 关键字在动态配置设置中的会话级别或本地级别声明变量。
在会话级别声明的变量使用 session 关键字，而在本地级别设置的变量使用 local 关键字。
使用以下命令设置名为 price 的会话变量。将代码复制并粘贴到你的终端中，然后按 Enter 按钮。
variable_db=# set session my.vars.price = '7000000';
SET
执行一个查询，使用我们声明的变量来查找价格为 7000000 的车辆。使用以下代码实现上述任务。
variable_db=# SELECT vehicle_name FROM vehicle WHERE vehicle_price = current_setting('my.vars.price')::int;
以下是执行上述查询的结果。
vehicle_name
---------------------------
Jaguar Range Rover Evoque
Porsche Cayenne
BMW 1 Series
(3 rows)
David is a back end developer with a major in computer science. He loves to solve problems using technology, learning new things, and making new friends. David is currently a technical writer who enjoys making hard concepts easier for other developers to understand and his work has been published on multiple sites.
LinkedIn GitHub