Assuming I have two array fields in a table, like this:
假设我在表中有两个数组字段,如下所示:
Column | Type |
--------+-----------+
index | integer[] |
value | integer[] |
Where both index and value are equal in length, and the values in 'index' are guaranteed to be unique, for example:
其中索引和值的长度相等,并且'index'中的值保证是唯一的,例如:
SELECT * FROM ArrayTest;
index | value
-----------+-----------------------------------
{1,3,5,6} | {100, 103, 105, 106}
How would I make a query which returns a new array, where the values in 'index' are used as array indexes, and the values in 'value' become the values associated with the given index, i.e. something like:
如何进行返回新数组的查询,其中'index'中的值用作数组索引,'value'中的值成为与给定索引关联的值,即:
SELECT some_array_function(index, value) as newarray;
new_array
--------------------------------
{100, NULL, 103, NULL, 105, 106}
What I want to achieve is the same as array_combine in PHP.
我想要实现的与PHP中的array_combine相同。
2 个解决方案
#1
3
While overall a bad idea to store data like this, one way to do it is to use the unnest function and the process the results on the client side:
虽然存储这样的数据总体上是一个坏主意,但一种方法是使用不需要的函数并在客户端使用结果处理结果:
SELECT unnest(index), unnest(value) FROM ArrayTest;
unnest | unnest
--------+--------
1 | 100
3 | 103
5 | 105
6 | 106
If you must have a function to create a new array here's a short function that will do it:
如果你必须有一个函数来创建一个新数组,这里有一个简短的函数:
CREATE FUNCTION array_stitch(index_array integer[], value_array integer[]) RETURNS integer[]
LANGUAGE plpgsql IMMUTABLE
AS $$
DECLARE
ptr INTEGER;
new_array INTEGER[];
BEGIN
FOR ptr IN 1..array_upper(index_array,1) LOOP
new_array[index_array[ptr]] := value_array[ptr];
END LOOP;
RETURN new_array;
END;
$$;
EDIT: Here's another way to do it:
编辑:这是另一种方法:
SELECT array_agg(val order by idx) as output_array
FROM (SELECT generate_series(1,idx[array_upper(idx,1)]) from array_test) as g(idx)
LEFT JOIN (SELECT unnest(idx) as idx, unnest(val) as val FROM array_test\
) as data_points USING (idx);
#2
1
Try this simple procedure:
试试这个简单的程序:
CREATE OR REPLACE FUNCTION merge_arrays(index INTEGER[], value INTEGER[]) RETURNS INTEGER[] AS $$
DECLARE
arr_size INTEGER;
max_index INTEGER;
result INTEGER[];
i INTEGER;
index_pos INTEGER := 1;
BEGIN
arr_size := array_upper(index, 1);
max_index := index[arr_size];
FOR i IN 1..max_index LOOP
IF index @> ARRAY[i] THEN
result := result || value[index_pos];
index_pos := index_pos + 1;
ELSE
result := result || '{NULL}'::INTEGER[];
END IF;
END LOOP;
RETURN result;
END;
$$ LANGUAGE PLPGSQL;
Usage:
用法:
SELECT merge_arrays(index ,value) from array_test;
#1
3
While overall a bad idea to store data like this, one way to do it is to use the unnest function and the process the results on the client side:
虽然存储这样的数据总体上是一个坏主意,但一种方法是使用不需要的函数并在客户端使用结果处理结果:
SELECT unnest(index), unnest(value) FROM ArrayTest;
unnest | unnest
--------+--------
1 | 100
3 | 103
5 | 105
6 | 106
If you must have a function to create a new array here's a short function that will do it:
如果你必须有一个函数来创建一个新数组,这里有一个简短的函数:
CREATE FUNCTION array_stitch(index_array integer[], value_array integer[]) RETURNS integer[]
LANGUAGE plpgsql IMMUTABLE
AS $$
DECLARE
ptr INTEGER;
new_array INTEGER[];
BEGIN
FOR ptr IN 1..array_upper(index_array,1) LOOP
new_array[index_array[ptr]] := value_array[ptr];
END LOOP;
RETURN new_array;
END;
$$;
EDIT: Here's another way to do it:
编辑:这是另一种方法:
SELECT array_agg(val order by idx) as output_array
FROM (SELECT generate_series(1,idx[array_upper(idx,1)]) from array_test) as g(idx)
LEFT JOIN (SELECT unnest(idx) as idx, unnest(val) as val FROM array_test\
) as data_points USING (idx);
#2
1
Try this simple procedure:
试试这个简单的程序:
CREATE OR REPLACE FUNCTION merge_arrays(index INTEGER[], value INTEGER[]) RETURNS INTEGER[] AS $$
DECLARE
arr_size INTEGER;
max_index INTEGER;
result INTEGER[];
i INTEGER;
index_pos INTEGER := 1;
BEGIN
arr_size := array_upper(index, 1);
max_index := index[arr_size];
FOR i IN 1..max_index LOOP
IF index @> ARRAY[i] THEN
result := result || value[index_pos];
index_pos := index_pos + 1;
ELSE
result := result || '{NULL}'::INTEGER[];
END IF;
END LOOP;
RETURN result;
END;
$$ LANGUAGE PLPGSQL;
Usage:
用法:
SELECT merge_arrays(index ,value) from array_test;