配列データに対する、要素の追加・削除方法

質問

PostgreSQLの配列に対して、要素の追加及び削除を行いたいのですが、下記のような動作をさせるには、※1と※2で、どのようなSQLを実行すれば良いでしょうか？

よろしくお願いいたします。

>>>
CREATE TABLE test (
id integer,
data integer[]
);

INSERT INTO test VALUES (1, '{0,1,2,3,4}');
INSERT INTO test VALUES (2, '{5,6,7,8,9}');

SELECT * FROM test;
id | data
----+-------------
1 | {0,1,2,3,4}
2 | {5,6,7,8,9}
(2 rows)

UPDATE test [data配列に10を追加] WHERE id = 1; /* ※1 */
UPDATE test [data配列から6を除去] WHERE id = 2; /* ※2 */

SELECT * FROM test;
id | data
----+-------------
1 | {0,1,2,3,4,10}
2 | {5,7,8,9}
(2 rows)
<<<

回答No.1

noname#249320 · ベストアンサー

PostgreSQL での配列は使ったことが無いのでちょっと調べてみました。

配列要素の追加は下記のように出来るようです。

UPDATE test SET data = array_append(data, 10);
UPDATE test SET data = data || ARRAY[10]

しかし, マニュアルに配列要素の削除についての項目が見当たりませんでした・・・。
結構検索してみたのですがどうにも見つけられず, 配列関数としてもサポートされていないようですし, もしかしたら無いのかも？ ^^;

# data - ARRAY[6] とかやってもだめでした。

参考URL：

http://sirius.itfrontier.co.jp/Powergres/product/doc/japanese20/functions-array.html

お礼

array_appendという関数があったのですね。気づきませんでした。ありがとうございます^^
除去の方は、やはり関数も無いようなので、CREATE FUNCTION に初挑戦してみました。

>>>
CREATE FUNCTION array_strip(integer[], integer) RETURNS integer[] AS '
　DECLARE
　　src ALIAS FOR $1;
　　val ALIAS FOR $2;
　　dst integer[] := ''{}'';
　　i integer;
　BEGIN
　　FOR i IN array_lower(src,1) .. array_upper(src,1) LOOP
　　　IF src[i] != val THEN
　　　　dst := array_append(dst, src[i]);
　　　END IF;
　　END LOOP;
　　RETURN dst;
　END;
' LANGUAGE 'plpgsql';

UPDATE test SET data = array_append(data, 10) WHERE id = 1;
UPDATE test SET data = array_strip(data, 6) WHERE id = 2;
<<<

以上のようになりましたが、もっと効率の良い方法、ありますかね…。

回答No.2

noname#249320

配列の要素を操作する関数が限られている以上, 配列の要素を全部サーチする以外に方法はないかと思います。
配列要素がそれなりに大きくなると負荷もあがりそうですし, 関数やプロシージャはデバッグが大変です ^^;

DB側ではなくフロント側で配列を操作するか, 配列データを正規化して別テーブルに分けるかにしたほうがいいかもしれません。