准备一张Book表
DROP TABLE IF EXISTS Book;
create table Book (
bid INT,
price INT,
pub_date varchar
);
insert into Book (bid, price, pub_date) values (1, 69, '10/22/2019');
insert into Book (bid, price, pub_date) values (2, 70, '12/14/2017');
insert into Book (bid, price, pub_date) values (3, 99, '10/6/2018');
insert into Book (bid, price, pub_date) values (4, 40, '11/21/2021');
insert into Book (bid, price, pub_date) values (5, 45, '7/7/2017');
insert into Book (bid, price, pub_date) values (6, 45, '2/17/2020');
insert into Book (bid, price, pub_date) values (7, 93, '7/26/2018');
insert into Book (bid, price, pub_date) values (8, 78, '11/11/2020');
insert into Book (bid, price, pub_date) values (9, 66, '1/11/2021');
insert into Book (bid, price, pub_date) values (10, 57, '4/9/2018');
insert into Book (bid, price, pub_date) values (11, 42, '6/13/2018');
insert into Book (bid, price, pub_date) values (12, 81, '10/7/2021');
insert into Book (bid, price, pub_date) values (13, 90, '12/22/2019');
insert into Book (bid, price, pub_date) values (14, 61, '6/20/2019');
insert into Book (bid, price, pub_date) values (15, 46, '6/20/2019');
insert into Book (bid, price, pub_date) values (16, 91, '4/7/2021');
insert into Book (bid, price, pub_date) values (17, 53, '9/20/2020');
insert into Book (bid, price, pub_date) values (18, 47, '8/28/2019');
insert into Book (bid, price, pub_date) values (19, 70, '4/16/2022');
insert into Book (bid, price, pub_date) values (20, 89, '3/27/2021');
查询结果为:
SELECT
bid AS 书本编号,
price AS 价格,
pub_date AS 出版日期
FROM
book;
将select查询结果存入数组
所有操作都是在函数/过程
中完成的,声明数组变量
需要在DECLARE
中
第一种方法
在声明时赋值
- 语法:
数组名 := ARRAY( SELECT 字段名 FROM 表名 WHERE 条件 );
- 将
Book
表中所有书的价格
存入数组:CREATE OR REPLACE PROCEDURE show_book_price () AS $$ DECLARE prices INT [] := ARRAY (SELECT price FROM Book); BEGIN raise notice 'array: % ', prices; -- 打印 prices 数组 END; $$ LANGUAGE plpgsql;
- 调用过程(
PROCEDURE
):CALL show_book_price ( );
第二种方法
与第一种方法类似,不过在
BEGIN
与END
内赋值
- 用户传入一个
最高价格highest_price
,只保存Book
表中价格低于highest_price
的书价,并且书价要高于平均价格CREATE OR REPLACE PROCEDURE show_book_price_conditon (highest_price INT) AS $$ DECLARE prices INT []; mean_price INT; BEGIN SELECT AVG(price) INTO mean_price FROM book; -- 将 select 结果存入 prices prices := ARRAY ( SELECT price FROM Book WHERE price < highest_price and price > mean_price ); raise notice '书本的平均价格 : %', mean_price; raise notice 'array: % ', prices; END; $$ LANGUAGE plpgsql;
- 调用过程:
CALL show_book_price_conditon ( 90 );
第三种方法
- 语法:
SELECT ARRAY_AGG(字段名::INT) INTO 数组名 FROM 表名 WHERE 条件;
- 与第二种方法的例子一样,用户传入一个
最高价格highest_price
,只保存Book
表中价格低于highest_price
的书价,并且书价要高于平均价格CREATE OR REPLACE PROCEDURE show_book_price_conditon (highest_price INT) AS $$ DECLARE prices INT []; mean_price INT; BEGIN SELECT AVG(price) INTO mean_price FROM book; -- 将 select 结果存入 prices SELECT ARRAY_AGG (price::INT) INTO prices FROM Book WHERE price < highest_price and price > mean_price; raise notice '书本的平均价格 : %', mean_price; raise notice 'array: % ', prices; END; $$ LANGUAGE plpgsql;
- 调用过程
CALL show_book_price_conditon ( 80 );
ARRAY_AGG()
ARRAY_AGG()
函数是一个聚合函数,它接受一组值并返回一个数组,其中输入集中的每个值都分配给数组的一个元素。
-
语法
ARRAY_AGG(表达式)
-
查询
Book
表的书本价格和出版日期,{price, pub_date}
作为数组的一个元素(一个二维数组)SELECT ARRAY_AGG ( price || pub_date ) FROM Book;
-
结果(有点长):
打印数组内容
看到这里相信大家都已经知道了
- 语法:
raise notice '数组的值 : %', 数组名;