PL/pgSQLの直接実行

質問

PL/pgSQLのことで教えてください。

これまで SQL Server を使っていましたが、PostgreSQL に移行しています。
Npgsql で、VB.Net から接続しようと思っています。

PL/pgSQLで、ストアドファンクションなどを定義するのではなく、PL/pgSQLスクリプトを
直接実行することはできないのでしょうか？

SQL Server では、T-SQLのスクリプトを直接実行できました。

例えば ManagementStudioのクエリ実行画面にスクリプトを入力して実行したり、
VBからだと、SqlCommand の CommandText に T-SQLを書いて実行したり、という
ことです。

pgAdminIIIのSQLエディタや、NpgsqlのNpgsqlCommandで同様のことをやろうとすると
エラーになります。

何か良い方法があるのでしょうか？

回答No.1

noname#212058 · ベストアンサー

できません。必ずストアドファンクションを作る必要があります。

以下の方法で、そのセッションだけで使用できる一時ファンクション (一時テーブルのファンクション版) が作れますので、これを使えば似たようなことはできます。面倒ですが、仕様です。代替手段があるだけマシと考えたほうが良いかと。

CREATE FUNCTION pg_temp.test(arg integer)
RETURNS integer AS $$
BEGIN
RETURN arg +1;
END;
$$ LANGUAGE 'plpgsql';

SELECT pg_temp.test(1);

DROP FUNCTION pg_temp.test(integer);

なお、Npgsql はライブラリ内部にコネクションプールを持つという特殊な仕様なので、利用される場合はご注意ください。デフォルトではコネクションを Close しても DB との接続は切断されません。

お礼

詳しいご回答ありがとうございました。

やはりそうなんですね・・・
一時ファンクションの線でやってみます。

スキーマの移行までは簡単に行ったのですが、やはり T-SQL → PL/pgSQL の移行が苦労しそうです。

コネクションプールの件も気をつけます。

SQL Serverの場合はプールされた接続を再利用するときは sp_reset_connection というストアドプロシージャが内部的に呼び出されて、使う側は何も気にしなくても使えるようになっていました。isolation levelが初期化されない等の問題はあるようですが、個人的には特に問題になったことは無かったです。

Npgsqlの動作はこれから詳しく調べますが、具体的にどのような問題が考えられるのでしょうか？

ちょっと見たところ、一時テーブルはちゃんと消えているようですね。（当たり前か(^^;)

回答No.2

noname#212058

> SQL Serverの場合はプールされた接続を再利用するときは (以下略)

コネクションプールを意識した実装をされたことがあるのであれば
大丈夫かと思います。「CLOSE してセッションを切れば、一時テー
ブルも自動的に破棄される」と思って DROP サボると大変なことに
なりますよ、程度の話ですので。

> Npgsqlの動作はこれから詳しく調べますが、具体的にどのような
> 問題が考えられるのでしょうか？

一般的にはコネクションプールの扱いくらいではないですかね。
あとは、私は timestamp の -inifinity 値を取得するときに、Npgsql
内部で強制的に日本時間に変換されて異常値が発生して困ったこ
とがありました。まぁ、Npgsql の全ソースを入手して VS.NET で
デバッグ実行できるので、すぐに原因特定できますけどね。

> スキーマの移行までは簡単に行ったのですが、やはり T-SQL →
> PL/pgSQL の移行が苦労しそうです。

プログラムの移行は、面倒ですが正直たいした話でないかと。
むしろ、最大の問題は「それっぽく動かすことができた後」かと
思います。

SQL Server と PostgreSQL はアーキテクチャが全然違いますので、
そのまま移行しただけでは実行パフォーマンスや運用に問題を抱える
可能性があります。このあたりの問題は、大きく手を入れる羽目に
なることも多いですからね…。
健闘をお祈りします。

お礼

shibataさん

ご回答ありがとうございました。
大変参考になりました。

SQL Serverは非常によく出来たDBだと思っていますが、コスト面を考えると
遅かれ早かれ移行しないといけないと思っています。

１ヶ月くらいで一気にできるかな、などと甘く考えていたのですが、それでは
済まなさそうなので、地道に少しずつやっていくことにします。