PostgreSQLでDOブロック内で動的SQLを実行した後にクエリの結果を取得したい

2024年6月7日2024年6月10日

今回はPostgreSQLでDOブロック内で動的SQLを実行した後にクエリの結果を取得したいときどのようにすれば良いのかについてご紹介いたします。

Contents

PostgreSQLでDOブロック内で動的SQLを実行した後にクエリの結果を取得したい
- DOブロックではなく、ストアドプロシージャを使用する
- EXECUTEクエリの結果を一時テーブルに格納する
終わりに

PostgreSQLでDOブロック内で動的SQLを実行した後にクエリの結果を取得したい

PostgreSQLで例えば下記のSQLを流したとき、「Query 1 OK: DO」と出るだけでSELECTなどの取得結果が出ないです。

DO $$ 
DECLARE
    cols text;
BEGIN
    SELECT string_agg(column_name, ', ')
    INTO cols
    FROM information_schema.columns
    WHERE table_name = 'test' AND column_name != 'column3';
    
    EXECUTE 'SELECT ' || cols || ' FROM test';
END $$;

DO $$

DECLARE

cols text;

BEGIN

SELECT string_agg(column_name, ', ')

INTO cols

FROM information_schema.columns

WHERE table_name = 'test' AND column_name != 'column3';

EXECUTE 'SELECT ' || cols || ' FROM test';

END $$;

上記のようなSQLで取得結果を取りたい場合、次の2つの方法があります。
・DOブロックではなく、ストアドプロシージャを使用する
・EXECUTEクエリの結果を一時テーブルに格納する

DOブロックではなく、ストアドプロシージャを使用する

ストアドプロシージャを作成してそこから動的SQLを実行し、結果を返す方法があります。

例えば次のようなコードになるでしょう。

CREATE OR REPLACE FUNCTION get_columns_except_column3()
RETURNS TABLE(column1 text, column2 text) AS $$
DECLARE
    cols text;
BEGIN
    SELECT string_agg(column_name, ', ')
    INTO cols
    FROM information_schema.columns
    WHERE table_name = 'test' AND column_name != 'column3';
    
    RETURN QUERY EXECUTE 'SELECT ' || cols || ' FROM test';
END;
$$ LANGUAGE plpgsql;

-- 呼び出し
SELECT * FROM get_columns_except_column3();

CREATE OR REPLACE FUNCTION get_columns_except_column3()

RETURNS TABLE(column1 text, column2 text) AS $$

DECLARE

cols text;

BEGIN

SELECT string_agg(column_name, ', ')

INTO cols

FROM information_schema.columns

WHERE table_name = 'test' AND column_name != 'column3';

RETURN QUERY EXECUTE 'SELECT ' || cols || ' FROM test';

END;

$$ LANGUAGE plpgsql;

-- 呼び出し

SELECT * FROM get_columns_except_column3();

EXECUTEクエリの結果を一時テーブルに格納する

DOブロック内でクエリの結果を一時テーブルに格納し、その後でそのテーブルから結果を取得する方法もあります。

DO $$
DECLARE
    cols text;
BEGIN
    SELECT string_agg(column_name, ', ')
    INTO cols
    FROM information_schema.columns
    WHERE table_name = 'test' AND column_name != 'column3';

    EXECUTE 'CREATE TEMP TABLE temp_results AS SELECT ' || cols || ' FROM test';
END $$;

-- 一時テーブルから結果を選択
SELECT * FROM temp_results;

DO $$

DECLARE

cols text;

BEGIN

SELECT string_agg(column_name, ', ')

INTO cols

FROM information_schema.columns

WHERE table_name = 'test' AND column_name != 'column3';

EXECUTE 'CREATE TEMP TABLE temp_results AS SELECT ' || cols || ' FROM test';

END $$;

-- 一時テーブルから結果を選択

SELECT * FROM temp_results;

一度実行してもう一度実行すると
Query 1 ERROR at Line 1: : ERROR: relation "temp_results" already exists
とエラーが出るので、
DROP TABLE IF EXISTS temp_results;
などで逐一削除しましょう。

個人的にはこれがおすすめです。