スポンサーサイト

上記の広告は1ヶ月以上更新のないブログに表示されています。
新しい記事を書く事で広告が消せます。

PL/pgSQLで行番号の入れ替え

コンボボックスに並ぶようなデータを入れ替える時、Webでは↑↓のようなボタンを押して順番を入れ替える方法があります。
また、直接移動したい行番号を指定して位置をいれかえることもあります。
これをSQLで考えてみたいと思います。

CREATE TABLE t_line(
user_id int
,line_id int
,line_no int
,name text
,delete_flag smallint
,PRIMARY KEY (user_id, line_id)
);

INSERT INTO t_line (user_id, line_id, line_no, name, delete_flag) VALUES
(1, 1, 1, 'one', 0)
,(1, 2, 2, 'two', 0)
,(1, 3, 3, 'three', 0)
,(1, 4, 4, 'four', 0)
,(1, 5, 5, 'five', 0);

SELECT * FROM t_line WHERE user_id = 1 AND delete_flag = 0 ORDER BY line_no;
1;1;1;"one";0
1;2;2;"two";0
1;3;3;"three";0
1;4;4;"four";0
1;5;5;"five";0

例えば上のような、テーブルとデータがある時、5番目のデータを1番目に挿入したいとします。
順番は5,1,2,3,4になるわけですが、5→1,1→2,2→3,3→4,4→5に行番号を入れ替えることになります。
SQLは一回の発行で済ませたいので、以下のようになります。

UPDATE t_line SET
line_no = CASE
WHEN line_no = 5 THEN 1
WHEN line_no >= 1 AND 5 > line_no THEN line_no + 1
ELSE line_no END
WHERE
user_id = 1;

また、削除を考えてみますと、削除された項目以下はすべて繰り上がることになります。
1,2,3,4,5の並びで2を削除すると、3→2,4→3,5→4で行番号を入れ替えることになります。
SQLは以下のようになります。

UPDATE t_line SET
delete_flag = CASE
WHEN line_no = 2 THEN 1 ELSE delete_flag END
,line_no = CASE
WHEN line_no > 2 THEN line_no - 1 ELSE line_no END
WHERE
user_id = 1;

このSQLだけでも十分便利なんですが、ここは無理矢理(笑)PL/pgSQLにしてみます。
■行番号を変更する

CREATE OR REPLACE FUNCTION uv_lineno_update (
p_table_name text
,p_line_column text
,p_from int
,p_to int
,p_where text DEFAULT ''
) RETURNS void AS $$
DECLARE
w_command text;
BEGIN
if NOT uv_is_set(p_table_name)
OR NOT uv_is_set(p_line_column)
OR p_from IS NULL
OR p_from < 1
OR p_to IS NULL
OR p_from < 1
OR p_from = p_to
THEN
RETURN;
END IF;

w_command := 'UPDATE ' || quote_ident(p_table_name) || ' SET ' ||
quote_ident(p_line_column) || ' = CASE WHEN ' || quote_ident(p_line_column) || ' = ' || p_from::text || ' THEN ' || p_to::text;

IF p_from < p_to THEN
w_command := w_command || ' WHEN ' || quote_ident(p_line_column) || ' > ' || p_from::text ||
' AND ' || quote_ident(p_line_column) || ' <= ' || p_to::text || ' THEN ' || quote_ident(p_line_column) || ' - 1';
ELSE
w_command := w_command || ' WHEN ' || quote_ident(p_line_column) || ' >= ' || p_to::text ||
' AND ' || quote_ident(p_line_column) || ' < ' || p_from::text || ' THEN ' || quote_ident(p_line_column) || ' + 1';
END IF;

w_command := w_command || ' ELSE ' || quote_ident(p_line_column) || ' END';

IF uv_is_set(p_where) THEN
w_command := w_command || ' WHERE ' || p_where;
END IF;

EXECUTE w_command;
END;
$$ LANGUAGE plpgsql;

■行番号を削除する

CREATE OR REPLACE FUNCTION uv_lineno_delete (
p_table_name text
,p_line_column text
,p_delete_column text
,p_lineno int
,p_where text DEFAULT ''
) RETURNS void AS $$
DECLARE
w_command text;
BEGIN
if NOT uv_is_set(p_table_name)
OR NOT uv_is_set(p_line_column)
OR NOT uv_is_set(p_delete_column)
OR p_lineno IS NULL
OR p_lineno < 1
THEN
RETURN;
END IF;

w_command := 'UPDATE ' || quote_ident(p_table_name) || ' SET ' ||
quote_ident(p_delete_column) || ' = CASE WHEN ' || quote_ident(p_line_column) ||
' = ' || p_lineno::text || ' THEN 1 ELSE ' || quote_ident(p_delete_column) || ' END, ' ||
quote_ident(p_line_column) || ' = CASE WHEN ' || quote_ident(p_line_column) ||
' > ' || p_lineno::text || ' THEN ' || quote_ident(p_line_column) || ' -1 ELSE ' || quote_ident(p_line_column) || ' END';

IF uv_is_set(p_where) THEN
w_command := w_command || ' WHERE ' || p_where;
END IF;

EXECUTE w_command;
END;
$$ LANGUAGE plpgsql;

使い方は、
SELECT uv_lineno_update('t_line', 'line_no', 5, 1, 'user_id = 1');
SELECT uv_lineno_delete('t_line', 'line_no', 'delete_flag', 2, 'user_id = 1');
のようになります。
今回のPL/pgSQLは動的SQLのサンプルになっています。
テーブル名とカラム名を渡せばよいので、行番号が必要なテーブルが増えるたびにSQLを追加する必要がありません。
テーブル構造が似ているSQLの再利用を考えると、動的SQLは便利に使えます。
あ、ちなみにWHERE句も引数として渡すことになりますが、これは直接SQLを組み立てるのに使うので、SQLインジェクションに注意してください。
スポンサーサイト

COMMENTS

COMMENT FORM

TRACKBACK


この記事にトラックバックする(FC2ブログユーザー)

上記広告は1ヶ月以上更新のないブログに表示されています。新しい記事を書くことで広告を消せます。