遍历每个元素从PostgreSQL的数组的数组 [英] Iterating on each element from an array of arrays on Postgresql
问题描述
我有我的存储过程中号的VARCHAR,这些数字被组织成数组,我将显示如下的例子:
{1,2,3,4,5,6,7,8,9},{1,2,3,4,5},{1,2,3 },{9} - 这是一个字符串
与另一个人帮助从这里我使用它来获取整型数组现在整数[]
SELECT string_to_array(regexp_split_to_table(
TRIM('{1,2,3,4,5,6,7,8,9},{1,2,3,4,5},{1,2,3},{9}','{ })
,},{),,):: INT []
我将有一组记录,他们每个人一个数组,见下图:
{} 1,2,3,4,5,6,7,8,9
{1,2,3,4,5}
{1,2,3}
{9}
我是想,但我想不通我怎么能做出一个的
来遍历这些阵列的每个元素调用另一个procecure从每个阵列中的每个元素做
我的阵列的一个例子{1,2,3,4,5,6,7,8,9}
,我会打电话给 my_array
:
REC记录;
ARR整数[];FOR REC在选定string_to_array(UNNEST(string_to_array(TRIM(text_nodes_for_connectivity,'{}'),'},{')),''):: INT [] LOOP
ARR:= array_cat(ARR,REC);
END LOOP;
我得到这个错误:
函数array_cat(整数[],记录)不存在
我需要我的每一个创纪录的业绩转换为一个数组,这样我就可以用array_cat或其他功能遍历数组元素
我的PROC code低于:
DROP FUNCTION IF EXISTS clustering_nodes();
CREATE OR REPLACE FUNCTION clustering_nodes()返回整数[] AS $$宣布
my_array整数[];
REC记录;
ARR整数[];
my_var VARCHAR [500];
LEN整数;开始my_var ='{1,2,3,4,5,6,7,8,9},{1,2,3,4,5},{1,2,3},{8}';
FOR REC在选定string_to_array(UNNEST(string_to_array(TRIM(my_var,'{}'),'},{')),''):: INT [] LOOP
LEN = array_length(REC);
ARR:= array_append(ARR,LEN);
END LOOP;RETURN ARR;结束;$$ LANGUAGE'PLPGSQL的严格;选择clustering_nodes();
提示或triks?
CREATE OR REPLACE FUNCTION clustering_nodes()
退货整数[] AS
$ FUNC $
宣布
my_var文本:='{1,2,3,4,5,6,7,8,9},{1,2,3,4,5},{1,2,3},{8}';
my_array整数[];
arr_len整数[];
开始FOR my_array IN
SELECT string_to_array(regexp_split_to_table(
修剪(my_var,'{}'),'},{'),''):: INT []
循环
arr_len:= array_append(arr_len,array_upper(my_array,1));
END LOOP;RETURN arr_len;结束
$ FUNC $ LANGUAGE PLPGSQL;
主要分
-
array_length()
不备案工作,只对数组。结果
更重要的是,有没有array_length()
中的Postgres 8.3 。该手册是在盘算,自己出去工具。使用不太有利array_upper()
代替。 -
<一个href=\"http://stackoverflow.com/questions/7462322/the-forgotten-assignment-operator-and-the-commonplace\">Assignment运营商PLPGSQL是
:=
。使用=
是未记录。 -
PLPGSQL
是语言PLPGSQL
,而不是字符串的标识符。不要引用它。可能会导致错误鬼鬼祟祟的。 -
您可以指定在声明时变量。
-
严格
修改是没有意义的不带参数。
用简单的Postgres现代
同样,这可以在一个单一的(如果有些复杂)呼叫有:
SELECT ARRAY_AGG(array_length(string_to_array(TXT,','),1))
从UNNEST(string_to_array(
修剪('{1,2,3,4,5,6,7,8,9},{1,2,3,4,5},{1,2,3},{8},{ })
,},{)
)AS子(TXT);
您需要升级到最新版本。
I have a VARCHAR of numbers inside my stored procedure, these numbers are organized as arrays, I will show an example below:
{1,2,3,4,5,6,7,8,9},{1,2,3,4,5},{1,2,3},{9} -- This is a STRING
Now with a help from another guy from here I'm using this to get integer arrays integer[]
SELECT string_to_array(regexp_split_to_table(
trim('{1,2,3,4,5,6,7,8,9},{1,2,3,4,5},{1,2,3},{9}', '{}')
, '},{'), ',')::int[]
I will have a set of records, each of them with an array, see below:
{1,2,3,4,5,6,7,8,9}
{1,2,3,4,5}
{1,2,3}
{9}
I was trying but I cannot figure out how can I make a FOR
to iterate over each element from these arrays to call another procecure to do with each element from each array.
An example for my array {1,2,3,4,5,6,7,8,9}
that I will call my_array
:
rec record;
arr integer[];
FOR rec IN SELECT string_to_array(unnest(string_to_array(trim(text_nodes_for_connectivity, '{}'), '},{')), ',')::int[] LOOP
arr := array_cat(arr, rec);
END LOOP;
I'm getting this error:
function array_cat(integer[], record) does not exist
I need to convert each of my record results to an array, so I can use array_cat or another functions to iterate over array elements
My proc code goes below:
DROP FUNCTION IF EXISTS clustering_nodes();
CREATE OR REPLACE FUNCTION clustering_nodes() RETURNS integer[] AS $$
DECLARE
my_array integer[];
rec record;
arr integer[];
my_var varchar[500];
len integer;
BEGIN
my_var = '{1,2,3,4,5,6,7,8,9},{1,2,3,4,5},{1,2,3},{8}';
FOR rec IN SELECT string_to_array(unnest(string_to_array(trim(my_var, '{}'), '},{')), ',')::int[] LOOP
len = array_length(rec);
arr := array_append(arr, len);
END LOOP;
RETURN arr;
END;
$$ LANGUAGE 'plpgsql' STRICT;
select clustering_nodes();
Tips or triks?
CREATE OR REPLACE FUNCTION clustering_nodes()
RETURNS integer[] AS
$func$
DECLARE
my_var text := '{1,2,3,4,5,6,7,8,9},{1,2,3,4,5},{1,2,3},{8}';
my_array integer[];
arr_len integer[];
BEGIN
FOR my_array IN
SELECT string_to_array(regexp_split_to_table(
trim(my_var, '{}'), '},{'), ',')::int[]
LOOP
arr_len := array_append(arr_len, array_upper(my_array, 1));
END LOOP;
RETURN arr_len;
END
$func$ LANGUAGE plpgsql;
Major points
array_length()
doesn't work on records, only on array.
What's more, there is noarray_length()
in Postgres 8.3. The manual is instrumental in figuring that out yourself. Using the less favorablearray_upper()
instead.Assignment operator in plpgsql is
:=
. Use of=
is undocumented.plpgsql
is an identifier inLANGUAGE plpgsql
, not a string. Do not quote it. May lead to sneaky errors.You can assign variables at declaration time.
STRICT
modifier is pointless without parameters.
-> SQLfiddle demo for Postgres 8.3.
Simpler with modern Postgres
Again, this could be had in a single (if somewhat complex) call:
SELECT array_agg(array_length(string_to_array(txt, ','), 1))
FROM unnest(string_to_array(
trim('{1,2,3,4,5,6,7,8,9},{1,2,3,4,5},{1,2,3},{8}', '{}')
, '},{')
) AS sub(txt);
You need to upgrade to a current version.
这篇关于遍历每个元素从PostgreSQL的数组的数组的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!