PostgreSQL系列生成与集合返回函数
2024-01-11 17:22:00 
阿炯
PostgreSQL的查询可能返回多于一行的函数，目前这个类中被使用最广泛的是级数生成函数，组合多集合返回。
系列生成函数：generate_series、下标生成函数：generate_subscripts。
9.25.集合返回函数(第13版)
Use Subscript Generating Functions(9.25.Set Returning Functions)
先看两个示例。
--------------------------------
CREATE TABLE freeoa.uns1 (
id int2 NOT NULL,
elements varchar(99) NULL,
CONSTRAINT uns1_pkey PRIMARY KEY (id)
);
INSERT INTO freeoa.uns1 (id,elements) VALUES
(1,'ab,cd,efg,hi'),
(2,'jk,lm,no,pq'),
(3,'rst,uvwx,yz');
SELECT id, unnest(elements) AS elem, generate_subscripts(elements, 1) AS nr
FROM (select id, string_to_array(elements, ',') AS elements FROM uns1) AS foo;
>
1 ab 1
1 cd 2
1 efg 3
1 hi 4
2 jk 1
2 lm 2
2 no 3
2 pq 4
3 rst 1
3 uvwx 2
3 yz 3
SELECT id, elements[i] AS elem, i AS nr
FROM (SELECT id, elements, generate_subscripts(elements, 1) AS i
FROM (select id, string_to_array(elements, ',') AS elements FROM uns1) AS foo) bar;
>
输出同上 ...
CREATE TABLE freeoa.uns2 (
id int2 NOT NULL,
elements _text NULL,
CONSTRAINT uns2_pkey PRIMARY KEY (id)
);
insert ...
SELECT id, unnest(elements) AS elem, generate_subscripts(elements, 1) AS nr from uns2;
>
输出同上 ...
generate_subscripts是一个快捷函数，它为给定数组的指定维度生成一组合法的下标。对于不具有请求维度的数组返回零行，对于任何输入为NULL数组也返回零行。
生成数组下标值：
# select generate_subscripts(array['a','b','c','d'],1);
1
2
3
4
# select generate_subscripts(array[array['a','b'],array['c','d'],array['e','f']],1);
1
2
3
# select generate_subscripts(array[array['a','b'],array['c','d'],array['e','f']],2);
1
2
再看一个比较久远的PostgreSQL官方例子(9.X之前的要处理此类问题时，9.4开始引入的WITH ORDINALITY后对查询结果的行中序列改善了很多)：v8.4-xfunc-sql
CREATE FUNCTION mleast(VARIADIC numeric[]) RETURNS numeric AS $$
SELECT min($1[i]) FROM generate_subscripts($1, 1) g(i);
$$ LANGUAGE SQL;
SELECT mleast(10, -1, 5, 4.4);
mleast
--------
-1
# select mleast(10.1,3,4,1.5);
mleast
--------
1.5
对其中的 SELECT min($1[i]) FROM generate_subscripts($1, 1) g(i)，感觉还是有一点费解的。没有关系，来作几个实验：
# CREATE or REPLACE FUNCTION mleast(VARIADIC numeric[]) RETURNS numeric AS $$
SELECT $1[2] FROM generate_subscripts($1, 1) g(i);
$$ LANGUAGE SQL;
CREATE FUNCTION
# select mleast(10.1,1.1,2.2,4.4);
mleast
--------
1.1
# CREATE or REPLACE FUNCTION mleast(VARIADIC numeric[]) RETURNS numeric AS $$
SELECT $1[3] FROM generate_subscripts($1, 1) g(i);
$$ LANGUAGE SQL;
CREATE FUNCTION
# select mleast(10.1,1.1,2.2,4.4);
mleast
--------
2.2
再把 g(i) 换个名称试验一下：
# CREATE or REPLACE FUNCTION mleast(VARIADIC numeric[]) RETURNS numeric AS $$
SELECT $1[2] FROM generate_subscripts($1, 1) gao(i);
$$ LANGUAGE SQL;
CREATE FUNCTION
# select mleast(10.1,1.1,2.2,4.4);
mleast
--------
1.1
# CREATE or REPLACE FUNCTION mleast(VARIADIC numeric[]) RETURNS numeric AS $$
SELECT min($1[i]) FROM generate_subscripts($1, 1) gao(i);
$$ LANGUAGE SQL;
CREATE FUNCTION
# select mleast(10.1,1.1,2.2,4.4);
mleast
--------
1.1
可以看到，所谓 g(i) 就是把 generate_subscripts($1,1) 的下标拿到，形成一个数组。第(i)个元素就是 g(i)。而min($1[i]) 却是个集合运算，此时 $1[i] 其实代表了 $1[1],$1[2],$1[3]... ，是集合。虽然这看上去有些诡异。
generate_series(start,stop,step)
step为正时，如果start大于stop则返回0行。相反当step为负时，如果start小于stop则返回0行。对于null输入也会返回0行。step为0是一个错误。
select current_date + s.a as dates from generate_series(0,14,7) as s(a);
dates
------------
2024-01-11
2024-01-18
2024-01-25
下标生成函数
generate_subscripts是一个快捷函数，它为给定数组的指定维度生成一组合法的下标。对于不具有请求维度的数组返回0行，对于null数组也返回行（但是会对null数组元素返回合法的下标）。
generate_subscripts (array anyarray, dim integer) → setof integer
Generates a series comprising the valid subscripts of the dim'th dimension of the given array.
generate_subscripts (array anyarray, dim integer, reverse boolean) → setof integer
Generates a series comprising the valid subscripts of the dim'th dimension of the given array. When reverse is true, returns the series in reverse order.
SELECT generate_subscripts('{NULL,1,NULL,2}'::int[], 1) AS s;
s
---
1
2
3
4
--------------------------------------
并行UNNEST()和PostgreSQL的排序顺序
使用：SELECT unnest(ARRAY[5,3,9]) as id;
没有ORDER BY子句，结果集的顺序是不能保证。
在PostgreSQL 9.3中关于下列要求的内容：
SELECT
unnest(ARRAY[5,3,9]) as id,
unnest(ARRAY(select generate_series(1, array_length(ARRAY[5,3,9], 1)))) as idx
ORDER BY idx ASC
是它保证了2 unnest()调用（其具有相同的长度）将在平行并且索引展开idx将确实匹配项的阵列中的位置？
这是Postgres的的特征和（如所有阵列具有相同数量的元素，只要）平行unnesting 保证是同步的。
Answer 1:
是的，这是Postgres的的特征和（如所有阵列具有相同数量的元素，只要）平行unnesting 保证是同步的。9.4增加了对并行UNNEST一个干净的解决方案：
并联UNNEST多个阵列
结果行的顺序不能保证，但事实上与语句一样简单：
SELECT unnest(ARRAY[5,3,9]) AS id
结果行的顺序是“保证”，但Postgres没有任何断言。查询优化器是无代价处理行，因为它认为合适的，只是顺序没有明确定义。这可能有更复杂的查询副作用。
如果在问题的第二个查询是你真正想要的东西（添加一个索引号嵌套的数组元素），还有一个更好的方法generate_subscripts()：
SELECT unnest(ARRAY[5,3,9]) AS id,generate_subscripts(ARRAY[5,3,9], 1) AS idx ORDER BY idx;
在这个相关答案详细介绍：
如何在PostgreSQL访问数组内部指标？
会有兴趣WITH ORDINALITY在Postgres的9.4：
的PostgreSQL UNNEST()与元件数
然后你可以使用：
SELECT * FROM unnest(ARRAY[5,3,9]) WITH ORDINALITY tbl(id,idx) order by idx desc;
Answer 2:
短的答案：否，idx接受的前提下，当将不匹配阵列位置，unnest()输出可以被随机排序。
演示：由于目前执行的unnest实际输出的行中元素的顺序，我建议加在它上面的一层来模拟随机顺序：
CREATE FUNCTION unnest_random(anyarray) RETURNS setof anyelement
language sql as
$$ select unnest($1) order by random() $$;
然后检查与您的查询的几次执行unnest替换unnest_random：
SELECT
unnest_random(ARRAY[5,3,9]) as id,
unnest_random(ARRAY(select generate_series(1, array_length(ARRAY[5,3,9], 1)))) as idx
ORDER BY idx ASC;
输出的例子：
id | idx
----+-----
3 | 1
9 | 2
5 | 3
id=3与相关联的idx=1，但3是在所述阵列中第二位置。这是完全错误的。
什么是错的查询：它假定第一unnest将使用相同的排列为第二洗牌元素unnest （排列在数学意义：该行的阵列和秩序秩序之间的关系）。但这个假设相矛盾的前提下，顺序输出unnest是不可预知的开始。
关于这个问题：是它保证了2 UNNEST()调用（其具有相同的长度）将并行展开。
在select unnest(...) X1, unnest(...) X2与X1和X2的类型为SETOF something和具有相同数目的行，X1和X2将在最终输出配对，使得X1值在行N将面临X2在相同的行值N。（它是列的一种联合，而不是一个笛卡尔积）。
但我不会形容这对组合在平行UNROLL，所以不知道这其中的意思。反正这对组合不符合问题的帮助，因为它发生后UNNEST电话已经失去了阵列位置。
一种替代方案：从的pgsql-SQL邮件列表中的功能建议：
CREATE OR REPLACE FUNCTION unnest_with_ordinality(anyarray, OUT value
anyelement, OUT ordinality integer)
RETURNS SETOF record AS
$$
SELECT $1[i], i FROM
generate_series(array_lower($1,1),array_upper($1,1)) i;
$$
LANGUAGE sql IMMUTABLE;
在此基础上可以通过第二输出列的顺序：
select * from unnest_with_ordinality(array[5,3,9]) order by 2;
value | ordinality
-------+------------
5 | 1
3 | 2
9 | 3
Postgres 9.4及以上版本：使用WITH ORDINALITY可依循返回函数调用将以一个通用的方式提供这种功能。
--------------------------------------
系列生成函数：generate_series、下标生成函数：generate_subscripts。
9.25.集合返回函数(第13版)
Use Subscript Generating Functions(9.25.Set Returning Functions)
先看两个示例。
--------------------------------
CREATE TABLE freeoa.uns1 (
id int2 NOT NULL,
elements varchar(99) NULL,
CONSTRAINT uns1_pkey PRIMARY KEY (id)
);
INSERT INTO freeoa.uns1 (id,elements) VALUES
(1,'ab,cd,efg,hi'),
(2,'jk,lm,no,pq'),
(3,'rst,uvwx,yz');
SELECT id, unnest(elements) AS elem, generate_subscripts(elements, 1) AS nr
FROM (select id, string_to_array(elements, ',') AS elements FROM uns1) AS foo;
>
1 ab 1
1 cd 2
1 efg 3
1 hi 4
2 jk 1
2 lm 2
2 no 3
2 pq 4
3 rst 1
3 uvwx 2
3 yz 3
SELECT id, elements[i] AS elem, i AS nr
FROM (SELECT id, elements, generate_subscripts(elements, 1) AS i
FROM (select id, string_to_array(elements, ',') AS elements FROM uns1) AS foo) bar;
>
输出同上 ...
CREATE TABLE freeoa.uns2 (
id int2 NOT NULL,
elements _text NULL,
CONSTRAINT uns2_pkey PRIMARY KEY (id)
);
insert ...
SELECT id, unnest(elements) AS elem, generate_subscripts(elements, 1) AS nr from uns2;
>
输出同上 ...
generate_subscripts是一个快捷函数，它为给定数组的指定维度生成一组合法的下标。对于不具有请求维度的数组返回零行，对于任何输入为NULL数组也返回零行。
生成数组下标值：
# select generate_subscripts(array['a','b','c','d'],1);
1
2
3
4
# select generate_subscripts(array[array['a','b'],array['c','d'],array['e','f']],1);
1
2
3
# select generate_subscripts(array[array['a','b'],array['c','d'],array['e','f']],2);
1
2
再看一个比较久远的PostgreSQL官方例子(9.X之前的要处理此类问题时，9.4开始引入的WITH ORDINALITY后对查询结果的行中序列改善了很多)：v8.4-xfunc-sql
CREATE FUNCTION mleast(VARIADIC numeric[]) RETURNS numeric AS $$
SELECT min($1[i]) FROM generate_subscripts($1, 1) g(i);
$$ LANGUAGE SQL;
SELECT mleast(10, -1, 5, 4.4);
mleast
--------
-1
# select mleast(10.1,3,4,1.5);
mleast
--------
1.5
对其中的 SELECT min($1[i]) FROM generate_subscripts($1, 1) g(i)，感觉还是有一点费解的。没有关系，来作几个实验：
# CREATE or REPLACE FUNCTION mleast(VARIADIC numeric[]) RETURNS numeric AS $$
SELECT $1[2] FROM generate_subscripts($1, 1) g(i);
$$ LANGUAGE SQL;
CREATE FUNCTION
# select mleast(10.1,1.1,2.2,4.4);
mleast
--------
1.1
# CREATE or REPLACE FUNCTION mleast(VARIADIC numeric[]) RETURNS numeric AS $$
SELECT $1[3] FROM generate_subscripts($1, 1) g(i);
$$ LANGUAGE SQL;
CREATE FUNCTION
# select mleast(10.1,1.1,2.2,4.4);
mleast
--------
2.2
再把 g(i) 换个名称试验一下：
# CREATE or REPLACE FUNCTION mleast(VARIADIC numeric[]) RETURNS numeric AS $$
SELECT $1[2] FROM generate_subscripts($1, 1) gao(i);
$$ LANGUAGE SQL;
CREATE FUNCTION
# select mleast(10.1,1.1,2.2,4.4);
mleast
--------
1.1
# CREATE or REPLACE FUNCTION mleast(VARIADIC numeric[]) RETURNS numeric AS $$
SELECT min($1[i]) FROM generate_subscripts($1, 1) gao(i);
$$ LANGUAGE SQL;
CREATE FUNCTION
# select mleast(10.1,1.1,2.2,4.4);
mleast
--------
1.1
可以看到，所谓 g(i) 就是把 generate_subscripts($1,1) 的下标拿到，形成一个数组。第(i)个元素就是 g(i)。而min($1[i]) 却是个集合运算，此时 $1[i] 其实代表了 $1[1],$1[2],$1[3]... ，是集合。虽然这看上去有些诡异。
generate_series(start,stop,step)
step为正时，如果start大于stop则返回0行。相反当step为负时，如果start小于stop则返回0行。对于null输入也会返回0行。step为0是一个错误。
select current_date + s.a as dates from generate_series(0,14,7) as s(a);
dates
------------
2024-01-11
2024-01-18
2024-01-25
下标生成函数
generate_subscripts是一个快捷函数，它为给定数组的指定维度生成一组合法的下标。对于不具有请求维度的数组返回0行，对于null数组也返回行（但是会对null数组元素返回合法的下标）。
generate_subscripts (array anyarray, dim integer) → setof integer
Generates a series comprising the valid subscripts of the dim'th dimension of the given array.
generate_subscripts (array anyarray, dim integer, reverse boolean) → setof integer
Generates a series comprising the valid subscripts of the dim'th dimension of the given array. When reverse is true, returns the series in reverse order.
SELECT generate_subscripts('{NULL,1,NULL,2}'::int[], 1) AS s;
s
---
1
2
3
4
--------------------------------------
并行UNNEST()和PostgreSQL的排序顺序
使用：SELECT unnest(ARRAY[5,3,9]) as id;
没有ORDER BY子句，结果集的顺序是不能保证。
在PostgreSQL 9.3中关于下列要求的内容：
SELECT
unnest(ARRAY[5,3,9]) as id,
unnest(ARRAY(select generate_series(1, array_length(ARRAY[5,3,9], 1)))) as idx
ORDER BY idx ASC
是它保证了2 unnest()调用（其具有相同的长度）将在平行并且索引展开idx将确实匹配项的阵列中的位置？
这是Postgres的的特征和（如所有阵列具有相同数量的元素，只要）平行unnesting 保证是同步的。
Answer 1:
是的，这是Postgres的的特征和（如所有阵列具有相同数量的元素，只要）平行unnesting 保证是同步的。9.4增加了对并行UNNEST一个干净的解决方案：
并联UNNEST多个阵列
结果行的顺序不能保证，但事实上与语句一样简单：
SELECT unnest(ARRAY[5,3,9]) AS id
结果行的顺序是“保证”，但Postgres没有任何断言。查询优化器是无代价处理行，因为它认为合适的，只是顺序没有明确定义。这可能有更复杂的查询副作用。
如果在问题的第二个查询是你真正想要的东西（添加一个索引号嵌套的数组元素），还有一个更好的方法generate_subscripts()：
SELECT unnest(ARRAY[5,3,9]) AS id,generate_subscripts(ARRAY[5,3,9], 1) AS idx ORDER BY idx;
在这个相关答案详细介绍：
如何在PostgreSQL访问数组内部指标？
会有兴趣WITH ORDINALITY在Postgres的9.4：
的PostgreSQL UNNEST()与元件数
然后你可以使用：
SELECT * FROM unnest(ARRAY[5,3,9]) WITH ORDINALITY tbl(id,idx) order by idx desc;
Answer 2:
短的答案：否，idx接受的前提下，当将不匹配阵列位置，unnest()输出可以被随机排序。
演示：由于目前执行的unnest实际输出的行中元素的顺序，我建议加在它上面的一层来模拟随机顺序：
CREATE FUNCTION unnest_random(anyarray) RETURNS setof anyelement
language sql as
$$ select unnest($1) order by random() $$;
然后检查与您的查询的几次执行unnest替换unnest_random：
SELECT
unnest_random(ARRAY[5,3,9]) as id,
unnest_random(ARRAY(select generate_series(1, array_length(ARRAY[5,3,9], 1)))) as idx
ORDER BY idx ASC;
输出的例子：
id | idx
----+-----
3 | 1
9 | 2
5 | 3
id=3与相关联的idx=1，但3是在所述阵列中第二位置。这是完全错误的。
什么是错的查询：它假定第一unnest将使用相同的排列为第二洗牌元素unnest （排列在数学意义：该行的阵列和秩序秩序之间的关系）。但这个假设相矛盾的前提下，顺序输出unnest是不可预知的开始。
关于这个问题：是它保证了2 UNNEST()调用（其具有相同的长度）将并行展开。
在select unnest(...) X1, unnest(...) X2与X1和X2的类型为SETOF something和具有相同数目的行，X1和X2将在最终输出配对，使得X1值在行N将面临X2在相同的行值N。（它是列的一种联合，而不是一个笛卡尔积）。
但我不会形容这对组合在平行UNROLL，所以不知道这其中的意思。反正这对组合不符合问题的帮助，因为它发生后UNNEST电话已经失去了阵列位置。
一种替代方案：从的pgsql-SQL邮件列表中的功能建议：
CREATE OR REPLACE FUNCTION unnest_with_ordinality(anyarray, OUT value
anyelement, OUT ordinality integer)
RETURNS SETOF record AS
$$
SELECT $1[i], i FROM
generate_series(array_lower($1,1),array_upper($1,1)) i;
$$
LANGUAGE sql IMMUTABLE;
在此基础上可以通过第二输出列的顺序：
select * from unnest_with_ordinality(array[5,3,9]) order by 2;
value | ordinality
-------+------------
5 | 1
3 | 2
9 | 3
Postgres 9.4及以上版本：使用WITH ORDINALITY可依循返回函数调用将以一个通用的方式提供这种功能。
--------------------------------------