[解決済み] PostgreSQL関数のパラメータとしてのテーブル名

質問

Postgresの関数のパラメータとしてテーブル名を渡したいです。私はこのコードを試してみました。

CREATE OR REPLACE FUNCTION some_f(param character varying) RETURNS integer 
AS $$
    BEGIN
    IF EXISTS (select * from quote_ident($1) where quote_ident($1).id=1) THEN
     return 1;
    END IF;
    return 0;
    END;
$$ LANGUAGE plpgsql;

select some_f('table_name');

で、こうなった。

ERROR:  syntax error at or near "."
LINE 4: ...elect * from quote_ident($1) where quote_ident($1).id=1)...
                                                             ^

********** Error **********

ERROR: syntax error at or near "."

そして、このように変更すると、以下のようなエラーが発生しました。 select * from quote_ident($1) tab where tab.id=1 :

ERROR:  column tab.id does not exist
LINE 1: ...T EXISTS (select * from quote_ident($1) tab where tab.id...

おそらく quote_ident($1) が機能しているのでしょう。 where quote_ident($1).id=1 の部分がなければ 1 と表示され、何かが選択されていることがわかります。なぜ、最初の quote_ident($1) は同時に動作し、2つ目のものは動作しないのでしょうか？また、どのようにこれを解決することができますか？

どのように解決するのですか？

さらに簡略化し、改善することができます。

CREATE OR REPLACE FUNCTION some_f(_tbl regclass, OUT result integer)
    LANGUAGE plpgsql AS
$func$
BEGIN
   EXECUTE format('SELECT (EXISTS (SELECT FROM %s WHERE id = 1))::int', _tbl)
   INTO result;
END
$func$;

スキーマで修飾された名前で呼び出す(下記参照)。

SELECT some_f('myschema.mytable');  -- would fail with quote_ident()

または

SELECT some_f('"my very uncommon table name"');

主なポイント

を使う OUT パラメーター を使って、関数を簡略化しています。動的SQLの結果を直接そこに選択すれば完了です。追加の変数やコードは必要ありません。

EXISTS はまさにあなたが望むことをします。あなたは true を、行が存在すれば false でなければ これには様々な方法がある。 EXISTS が一般的に最も効率的です。

あなたは 整数 を返したいようなので boolean の結果を EXISTS から integer に変更すると、あなたが持っていたものと全く同じものが得られます。私なら ブーリアン の代わりに

私は、オブジェクト識別子型の regclass の入力タイプとして _tbl . これは、すべての quote_ident(_tbl) または format('%I', _tbl) でも良いのですが、より良いからです。

• ... それは SQL インジェクション をちょうどよく防げます。

• ... テーブル名が無効/存在しない/現在のユーザから見えない場合、即座に、より優雅に失敗します。(A regclass パラメータは 既存の テーブルに対してのみ適用されます)。

• ... それはスキーマで修飾されたテーブル名で動作し、ここでプレーンな quote_ident(_tbl) または format(%I) などとすると、曖昧さを解消できないので失敗します。スキーマ名とテーブル名を別々に渡したり、エスケープしたりする必要があります。

に対してのみ機能します。 既存の テーブルに対してのみ動作します。

私は今でも format() は、構文を単純化するため（そしてどのように使われるかを示すため）、しかし %s の代わりに %I . 一般に、クエリはより複雑なので format() の方が役に立ちます。単純な例では、単に連結することもできます。

EXECUTE 'SELECT (EXISTS (SELECT FROM ' || _tbl || ' WHERE id = 1))::int'

をテーブル修飾する必要はありません。 id カラムの中にテーブルが一つしかない場合は、テーブルクオリフィケーションは必要ありません。 FROM リストにはテーブルが一つしかないにもかかわらずです。この例では、あいまいな表現はできません。(動的) SQL コマンドを EXECUTE には 分離したスコープ であるため、関数の変数やパラメータはそこでは見えません - 関数本体での普通の SQL コマンドとは対照的です。

ここで、なぜ 常に エスケープする理由は以下の通りです。

db<>fiddle ここで SQLインジェクションのデモ

_{古い sqlfiddle}