タグ: postgresql

同じIDのTITLEデータをカンマ区切りでとってきたい！
とか思って漁っていたら、

MySQLでidだけをSELECTして、カンマ区切りにして出力する

という、ページが見つかった。
GROUP_CONCATという関数を使い、区切り文字をカンマに指定する方法である。

SELECT GROUP_CONCAT(title SEPARATOR ',') FROM t_sample WHERE id = 1;

PostgreSQLだと、ARRAY_TO_STRING

＜参照＞PostgreSQL覚書 配列

昔やったのに忘れてしもーた・・・
と、Webで調べたDBLINK。

「CREATE EXTENSION DBLINK;とやったら使えるよ！」みたいなサイトがあったからやってみたら、postgreさんに「なんじゃい！」って怒られたので、過去のドキュメントを引っ張り出してきた。

「そもそも、モジュールあらへんやん、あんた」みたいな話なので、
コマンドプロンプトを立ち上げて、

cd /d C:\Program Files\PostgreSQL\9.0
bin\psql -U {USER_ID} -d {DBNAME} < share\contrib\dblink.sql でPWを入れてDBLINKを使えるようにすると、関数としてDBLINKが使えるようになった。

1年分の日付データを作りたい。もちろん一括で。
できないものかと探すと、あった。

SELECT arr.i AS date1
 FROM
 generate_series(
   cast('2016-1-1' as timestamp)
 , cast('2016-1-1' as timestamp) +  '12 months' + '-1days'
 , '1days'
 ) as arr(i)

Series Generating Functions(集合を返す関数）・・・

よくわかりヘンが、generate_seriesの1つめの引数をスタートとして、2つ目の引数まで1データずつレコードを作成してくれる便利なもの。
1,2の引数がINT型であれば、3つ目の引数はなく、＋1しながらレコードが作られる。
1,2の引数がTIMESTAMP型であれば、3つ目の引数にインターバル設定をして、インターバル設定分プラスしながらレコードが作られる。（上の例だと1日ずつ）

参考URL：Postgresqlでカレンダーを使わずに日付を列挙する方法(generate_series)

主キーの設定と解除のSQL。

ALTER TABLE {TABLE} ADD CONSTRAINT {KEY} PRIMARY KEY ({COLUMNS});
ALTER TABLE {TABLE} DROP CONSTRAINT {KEY};

{TABLE}：テーブル名
{KEY}：主キー名
{COLUMNS}：列名(複数の場合はカンマで区切って指定）

これは一般的な１か月後

SELECT current_timestamp + interval  '1months'

動的に算出する場合の例

SELECT current_timestamp + CAST(a.add_months || 'months' AS interval)
FROM table_a as a

CASTを簡単に書くと

SELECT current_timestamp + (a.add_months || 'months')::interval
FROM table_a as a

という感じ。

dqyファイルは便利だ。
わざわざ、PhpAdminやらManagerやら開けずにSQL文を実行して、データが確認できる。

自分がよく使うPostgreSQLとSQLServerのクエリファイルの書き方メモ。

PostgreSQL

XLODBC
1
Driver=PostgreSQL Unicode;Server={IPアドレス等};Port={PortNo};Database={DB名};Uid={UserID};Pwd={Password};
SELECT TOP 100 * FROM batch_log ORDER BY create_date desc

SQLServer

XLODBC
1
Driver=SQL Server;Server={IPアドレス等};Uid={UserID};Pwd={Password};Database={DB名};Connect Timeout=15;
SELECT * FROM batch_log ORDER BY create_date desc LIMIT 1;

SELECT文は改行すると動作しないときがあるので注意

関数の戻り値というと、普通は１つの値なのだけれど、複数行とってきたいとか、テーブルとしてとってきたいとか思う。

問い合わせ言語（SQL）関数

を見ているとできなくないらしいけれど、

CREATE FUNCTION sum_n_product_with_tab (x int)
    RETURNS TABLE(sum int, product int) AS $$
    SELECT $1 + tab.y, $1 * tab.y FROM tab;
$$ LANGUAGE SQL;

で、LANGUAGEが「SQL」となっていて、「plpgsql」で作っているものだとRETURNSの後ろをTABLE(ほにゃほにゃ)に変えただけではうまくいかなかった。
戻り値が
sum | product
—–+——————
50 | aaa
66 | bbb

とかになってほしいんだけど、
sum_n_product_with_tab
———————–
( 50, aaa)
( 66, bbb)
になってしまって、せっかくTABLEにしている意味ないんじゃない？ということになった。

ふむ。どうすればよいのじゃ・・・

ということで、せめて複数行とってくることにしよう。

CREATE OR REPLACE FUNCTION hoge(parm_cd character varying)
  RETURNS numeric  AS
$BODY$
DECLARE
    ・・・・
BEGIN

  CREATE TEMP TABLE wk_master(code numeric(2,0))
  ・・・・
  RETURN QUERY SELECT code FROM wk_master;
  DROP TABLE wk_master;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;

RETURN QUERY・・・とすると、複数行で返してくれる。

今日はこの辺で終了。

またあっという間に時が経った。
師走だ。

WindowsバッチでAサーバにあるDBをBサーバに移すことにした。

んで、とりあえずいろいろ参考にして作ったのが下の例。

まずはパスワードを設定しておいて、途中で聞かれないようにしてから・・・
pg_dump を使って D:\backup\test.backupに 111.111.111.111:5432 のA1_DBをバックアップ。
(ユーザはpostgres)
バックアップファイルからpg_restoreを使って、111.111.111.123:5432 のA2_DBへリストア。
(こっちもユーザはpostgres)

—————————————————————–
SET PGPASSWORD=password
“C:\Program Files\PostgreSQL\9.3\bin\pg_dump.exe” -f D:\backup\test.backup -i -v -h 111.111.111.111 -p 5432 -U “postgres” -Fc “A1_DB”
if not %ERRORLEVEL%==0 stop

“C:\Program Files\PostgreSQL\9.3\bin\pg_restore.exe” -c -h 111.111.111.123 -p 5432 -U “postgres” -Fc -d “A2_DB” D:\backup\test.backup

stop
—————————————————————–
オプション関連はドキュメントを見てもらえばいいと思うけど、書き順やらがドキュメントではいまいちわからんかったのでメモっとく。

いつのまにか10月になって、いつのまにかWordPressがVer4.0になってた。
時の経つのは早いもの。

PostgreSQLにはIsNumericとかIsNumberみたいな関数はないのねぇ・・・
Function作ればいいんだよ！って、言われてもねぇ・・・

ということで、代替案を漁ってみた。

SELECT (CASE WHEN emp_cd ~'^[0-9]*$' THEN emp_cd ELSE NULL END) emp_cd FROM master_table

ってな感じで、正規表現を使うらしい。
関数作って使ったほうが楽じゃね？って言わないでね・・・

昔バグった話。
接続しっぱなしでロジックを終わらせてたら、どんどん接続数が増えて処理できなくなった。
CLOSEすんのを忘れてたんです。スイマセン。

で、大騒ぎになる前に。

SELECT * FROM pg_stat_activity
 ORDER BY usename,application_name;

で、ちゃんとクローズしたことを確認する。
application_nameがpgAdmin以外で、IPアドレスを確認して、current_queryがアイドル状態になっておらず、SQL文が設定されたままだとクローズしていない。
処理が終わってもこういうのが残っていると、ちゃんと切れてないので切断すること。

このバグのせいで、運用問題にまで発展しテンヤワンヤとなったので、メモ。

副クエリーを使ったUPDATEの方法。

扱っているブログは結構あるものの、複数項目をUPDATEする方法について書いてあるものが少ない気がする。

UPDATE table1 t1
 SET (data1,data2,data3)=(t2.data1,t2.data2,t3.data3)
 FROM table2 t2
 WHERE t1.xx_cd = t2.xx_cd;

10年前にOracleで作成して以来、久しぶりにトリガーを作成してみた。
しかも、PostgreSQLで。

いろいろできなかったことがあったので調べてみたが、結局わからないところは代替案（要するに適当）で作った。
トリガー作成なんて、今後もあまりやらないだろうから、覚書は細かく書いておくことに。

まず、Functionを作ってからTriggerを作成。

--------------------------------------------------------
-- 走らせる処理はこっちに書く(言語はplpgsql)
--------------------------------------------------------
CREATE OR REPLACE FUNCTION SET_XX_FUNC()
  RETURNS trigger AS $$
    DECLARE
        -- 変数定義 --
    BEGIN
        -- 処理 --
    END;
$$ LANGUAGE ’plpgsql’;

--------------------------------------------------------
-- {table}に設定したテーブルに行が挿入、または行更新された後に処理を走らせる
--------------------------------------------------------
CREATE OR REPLACE TRIGGER SET_XX
 AFTER INSERT OR UPDATE ON {table} FOR EACH ROW
 EXECUTE PROCEDURE SET_XX_FUNC();

さて、ここまでは良い。
こっからだ。

--------------------------------------------------------
--★ 変数の設定
--------------------------------------------------------
DECLARE item_a int;
DECLARE item_b int ：=999 ;
item_a := 1;

＞＞1つ目は変数のみ定義。
＞＞2つ目は変数定義と同時に初期値を設定する方法。
＞＞3つ目は定義した変数に値を入れる方法。

--------------------------------------------------------
--★ 情報の取得方法その１（普通のSELECT文）
--------------------------------------------------------
DECLARE item_a int;
DECLARE item_b int;
DECLARE item_c int;
-- (中略) --
SELECT a,b,c INTO item_a,item_b,item_c 
FROM {table} WHERE key1=NEW.key1 ;

＞＞SELECT文で取得したデータを変数に入れる場合の書き方はこんな感じ。
　　SELECT a INTO item_a, b INTO item_b … ってな書き方はNG。
＞＞トリガーで更新・挿入したデータの情報を取得する場合は、NEW.{カラム名}で。

--------------------------------------------------------
--★ 情報の取得方法その２（Executeを利用したSELECT文）
--------------------------------------------------------
DECLARE add_months text = ’2months’;
DECLARE next_kigen text = ’’;
-- (中略) --
EXECUTE ’SELECT TO_CHAR(
　　　　　　　　　　　DATE_TRUNC(’’month’’
　　　　　　　　　　　　　　　　, CURRENT_TIMESTAMP 
	               || ’ + interval ’ || quote_literal(add_months) || ’)’ 
	               || ’ + interval ’’-1days’’,’’YYYYMMDD’’)’
 INTO next_kigen; 

＞＞普通のSELECT文ではどうしても無理なものは、文字列のSELECT文を実行することになる。
　　上の例は、現在日時から翌月末の日付(+2ヶ月-1日)を取得する方法。
　　add_monthsがころころ変わる場合（3か月後、4か月後と）、普通のSELECT文ではどうしてもできなかった。
　　あとは、サブクエリ―を使う場合もSELECT文では書けないと思っておいたほうがよい。
　　（テーブル別名＝エイリアスが使えないので。）
　　なので、文字列を生成して
　　　　　　　EXECUTE 文字列 INTO 変数名;
　　で検索結果をINTOの後ろの変数に格納させる。
　　文字列内のシングルクォーテーションは’’と、2連続にすること。
＞＞quote_literal関数はよくわからないが、変数の型にあったquateを設定すると思う。
　　文字列の間で変数を使いたいときは、必ずこの関数を使う。（使わんかったら動かんかったし）
　　ちなみに、DATE_TRUNC(‘month’,{date_type})は月初を求めるときの関数。

--------------------------------------------------------
--★ 更新後の{item1}が0以下なら処理をやめる（条件文の例）
--------------------------------------------------------
IF NEW.{item1} <= 0 THEN
　　RETURN NEW;
END IF;

＞＞途中で処理を抜ける処理の書き方がイマイチわからなかったので、とりあえずRETURN NEWしてる。
　　（正しい方法を教えて！！）

--------------------------------------------------------
--★ 検索結果がないときの判定
--------------------------------------------------------
IF NOT FOUND THEN
　　RETURN NEW;
END IF;

＞＞なのだが、これってカーソル処理やFOR文でしか作動しないように思う。

——————————————————–
–★ OLDとNEW
——————————————————–
OLDは更新前のレコードで、NEWは更新後のレコード。
NEW.{カラム名} という感じで使う。
なので、DELETEのトリガーの場合はOLDしかないし、INSERTの場合はNEWしかない。
今回は、AFTER INSERT OR UPDATE ～つまり更新・挿入後に行う～の処理なので、NEWしか使っていない。

衰えた脳みそをフル活用させて1日がかりで作ったのだが、うまく動かなかった　・・・・

で、デバッグ。
本当はログ出力して経過をみたかったのだけれど、ログ出力の仕方がよくわからないし、テスト環境と本番環境で異なる設定をしてもなぁ・・・と思って、動作途中で強制エラーを発生させることにした。

--------------------------------------------------------
--★ RAISE EXCEPTION（強制的に例外を発生させる）
--------------------------------------------------------
DECLARE add_months text = ’2months’;
-- (中略) 
RAISE EXCEPTION '% ここまで終わったぜ', add_months;

＞＞エラーメッセージに「2months ここまで終わったぜ」と表示される。
　　（%は可変値で、2つ目以降の引数で設定する。Cとかの%dとかと同じ）
　　なので、とりあえず、ここまでは走ってるよなぁ・・・ということで、
　　次はこの例外発生処理を後ろに移動させてから実行する。
　　これを何度か繰り返して、なんとか思い通りに動作するところまで行った。

とりあえず、直近で必要な情報はこんなところ。
今回はカーソルとかは使わなかったので、使うときはまた追記なり投稿なりしようと思う。
EXECUTEとかの使い方が難しいし、面倒だった。

まぁ、こんだけ書いておけば、次回はさほどオロオロすることもないだろう。

SQLServerとPostgreSQLを行ったり来たりしてると、どうもこんがらがって困る。

＜現在日付を8桁にする＞
to_char(current_timestamp,’YYYYMMDD’)::numeric

SQLServerだとCONVERTを使うんで、CONVERT・・・・と書きそうになった。

つづけて、1つのカラムの中にカンマ区切りでデータが入っていた場合に、
これを縦にする方法。

SELECT regexp_split_to_table(item_cd,’,’) item_cd FROM table_name

item_cdカラムに「a,b,c」と入力されているとすると、出力は

a
b
c
となる。

財布を落とした。
警察へ行った。
紛失届を作成するのにあれやこれや聞かれて、答えれば答えるほど（なくしたものの大きさに）堪える・・・
って、こんな時にもダジャレが出るぐらいに凹んでる。

さて、仕事しよう。

data1,data2,data3 というカラムがあるとして、それを

data1
data2
data3
と縦に並べたいときのやり方。

SELECT unnest(array[data1, data2, data3]) as data_col
FROM table_name;

PostgreSQLで現在の接続数をみたい場合のSQL文

詳細
SELECT * FROM pg_stat_activity
ORDER BY query_start;

サマリー
SELECT datname, usename, COUNT(*)
FROM pg_stat_activity
GROUP BY datname, usename;

文字型のフィールドを数値化して並び替えする場合はキャストするらしい。

ORDER BY cast( [文字型フィールド] as int)

って感じ。
（[文字型フィールド] をフィールド名に変える。）

このブログのPostgreSQL情報もどんどん増えてきていい感じだ。

PostgreSQLは配列を使えるので、簡単な書き方が何かあるはずだと思っていたら、やっぱりあった。

SELECT
TO_CHAR(current_timestamp,’YYYY年MM月DD日’) ||
(ARRAY[‘(日)’,'(月)’,'(火)’,'(水)’,'(木)’,'(金)’,'(土)’])
[extract(‘dow’ FROM current_timestamp) + 1] as now_date

“2013年04月18日(木)”

ってな感じで表示される。
便利だなぁ～～～

8桁で日付を表しているときの日付計算について

下の例では、8桁日付のところを文字列にしているが
　COALESCE(TO_CHAR(20120921,’99999999′),’99991231′)
とか置きかえてやれば、数値でも転用できる。

——————————————

SELECT to_char(to_timestamp(‘20120921′,’YYYYMMDD’) + ’20 days’,’YYYYMMDD’) as add_date;

とすると、結果は “20121011” と返ってくる。

SELECT to_char(to_timestamp(‘20120921′,’YYYYMMDD’) + ’20 days’,’YYYYMM01′) as add_date;

とすると、結果は “20121001” と返ってくるので月初めの日付を取得することができる。

要は、TO_TIMESTAMPメソッドでタイムスタンプ型にしてやることのようだ。
TO_DATEでは計算はできなかった。

[2013/05/09追記]
select to_date(20120921::text,’YYYYMMDD’)
::で型変換(キャスト)してしまえば、もっと楽ってわかった。

私はPostgreSQLが好きだ。
無料だし、機能も結構充実してる。
MySQLのGUI管理画面はいかにも重そうなのだけど、PostgreSQLのはそんなに重くない。
（よく強制終了しているけど）

この間、他の人の書いたソースコードにRETURNING句があった。
一目見ただけで、何のためのものかは分かったのだが、
「こんなことできるんだ・・・」
と衝撃的だった。
INSERT INTO ….. RETURNING A,B
とかにすると、挿入したデータのカラム値を配列で返してくれるそうな。

便利だ！
Nextval()とか、CurVal()とかしなくてもいいのね！
なんて素敵なんだ！