PostgreSQLで無名ブロック外の変数を参照したい

Question

実現したいこと

PostgreSQLで無名ブロック外の変数を参照したいです。

発生している問題・分からないこと

下記ソースコードを実行すると、構文エラーが出てしまいます。
ブロック外の変数は参照できないのか、変数取得方法が間違っているのかがわかりません。

:'var1';
^
”:”またはその付近で構文エラー

該当のソースコード

\prompt '入力した値：' var1

DO
$$
    DECLARE 
        value_txt text;
    BEGIN
        value_txt := :'var1';
        raise info 'value_txt=%' , value_txt;
END
$$;

試したこと・調べたこと

• teratailやGoogle等で検索した
• ソースコードを自分なりに変更した
• 知人に聞いた
• その他

上記の詳細・結果

ブロック外の変数を参照するやり方を検索してみましたが出ず、ChatGPTで検索すると、上記でできるような記述がありました。
他に変数の値を取得するために、「var」や「:var1」を試してみましたが、結果は同じでした。

補足

PostgreSQL 13.16

Comments

[sazi]

> \prompt '入力した値：' var1
を実行していないからではないですか？

無名ブロックにパラメータを渡すような事を行いたいという事だと思いますが、そのパラメータは主に環境変数経由ということになりますが、その環境変数を設定する処理を実施していないのではないでしょうか？

[iwanori]

ご回答ありがとうございます。

\set var1 test
上記のように変数設定の処理を代わりに入れましたが、結果は同じでした。

[melian]

ブロック外の変数を参照する方法がありませんので、関数の引数として渡すしかないと思います。

\prompt '入力した値：' var1

CREATE OR REPLACE FUNCTION show_input_value(value text DEFAULT :'var1') RETURNS void AS
$$
DECLARE
value_txt text;
BEGIN
value_txt := value;
raise info 'value_txt=%' , value_txt;
END
$$ LANGUAGE plpgsql IMMUTABLE;

select show_input_value();
# select show_input_value(:'var1');

[iwanori]

ご回答ありがとうございます。

諸事情により、プロシージャや関数は作らないように無名ブロックで動作させたかったのですが、こちらの方法しかないようですね。
一連の処理が終わったら、関数を削除するという方法も検討してみます。

[sazi]

無名ブロックはサーバー上で実行されますので、psqlなどのクライアントで設定した変数は、サーバーでは認識できないのでエラーになります。

> プロシージャや関数は作らないように無名ブロックで動作させたかった
対応としては、無名ブロック自体を文字列として編集して実行する方法が考えられます。
\set var1 'test'
\set my_block 'DO $$ BEGIN RAISE INFO ''var1: %'', ' :'var1' '; END $$;'
:my_block

無名ブロックが長文になるようなら、SQLファイルにして、バッチやシェルからパラメータ渡しにする方が保守性は上がるかと思います。

[iwanori]

試したところ望んだ結果になりました！
ベストアンサーに選びたいので同じ内容を回答欄に投稿いただけますでしょうか？

Answer 1

無名ブロックはサーバー上で実行されますので、psqlなどのクライアントで設定した変数は、サーバーでは認識できないのでエラーになります。

プロシージャや関数は作らないように無名ブロックで動作させたかった

対応としては、無名ブロック自体を文字列として編集して実行する方法が考えられます。

\set var1 'test'
\set my_block 'DO $$ BEGIN RAISE INFO ''var1: %'', ' :'var1' '; END $$;'
:my_block

無名ブロックが長文になるようなら、SQLファイルにして、バッチやシェルからパラメータ渡しにする方が保守性は上がるかと思います。