Gemini: PostgreSQL の SQL を作成

こちらの質問に対する Gemini の一次回答です。
生成AI で PostgreSQL の SQL を作成

-- テーブルAの名前
TABLE_A_NAME = 'your_table_name';

-- スキーマ名
SCHEMA_NAME = 'your_schema_name';

-- 1. スキーマ内のテーブルBをすべて検索し、テーブルAのすべての列を含むものを見つける
WITH potential_table_b AS (
  SELECT table_name
  FROM information_schema.tables
  WHERE table_schema = SCHEMA_NAME
    AND table_name != TABLE_A_NAME
)
SELECT *
FROM potential_table_b
WHERE (
    SELECT count(*)
    FROM information_schema.columns
    WHERE table_schema = SCHEMA_NAME
      AND table_name = potential_table_b.table_name
      AND column_name IN (
        SELECT column_name
        FROM information_schema.columns
        WHERE table_schema = SCHEMA_NAME
          AND table_name = TABLE_A_NAME
      )
) = (
    SELECT count(*)
    FROM information_schema.columns
    WHERE table_schema = SCHEMA_NAME
      AND table_name = potential_table_b.table_name
);

-- 2. 見つかった場合、テーブルBのNOT NULL列がすべてテーブルAに含まれているかどうかを判定する
IF (FOUND_ROWS() > 0) THEN
  -- テーブルBの名前を取得
  TABLE_B_NAME = (SELECT table_name FROM potential_table_b LIMIT 1);

  -- テーブルBのNOT NULL列をすべて取得
  NOT_NULL_COLUMNS_B = (
    SELECT column_name
    FROM information_schema.columns
    WHERE table_schema = SCHEMA_NAME
      AND table_name = TABLE_B_NAME
      AND is_nullable = 'NO'
  );

  -- テーブルAにNOT NULL列Bが存在するかを判定
  COLUMN_EXISTS = (
    SELECT count(*)
    FROM information_schema.columns
    WHERE table_schema = SCHEMA_NAME
      AND table_name = TABLE_A_NAME
      AND column_name IN (NOT_NULL_COLUMNS_B)
  ) = (
    SELECT count(*)
    FROM NOT_NULL_COLUMNS_B
  );

  -- 結果を返す
  SELECT CASE WHEN COLUMN_EXISTS THEN TRUE ELSE FALSE END AS result;
ELSE
  -- テーブルBが見つからない場合はFalseを返す
  SELECT FALSE AS result;
END IF;

実行結果

ERROR:  syntax error at or near "TABLE_A_NAME"
行 1: TABLE_A_NAME = 'table_a';
      ^
ERROR:  syntax error at or near "SCHEMA_NAME"
行 1: SCHEMA_NAME = 'sch001';
      ^
ERROR:  column "schema_name" does not exist
行 4:   WHERE table_schema = SCHEMA_NAME
                             ^
ERROR:  syntax error at or near "IF"
行 1: IF (FOUND_ROWS() > 0) THEN
      ^
ERROR:  syntax error at or near "NOT_NULL_COLUMNS_B"
行 1: NOT_NULL_COLUMNS_B = (
      ^
ERROR:  syntax error at or near "COLUMN_EXISTS"
行 1: COLUMN_EXISTS = (
      ^
ERROR:  column "column_exists" does not exist
行 1: SELECT CASE WHEN COLUMN_EXISTS THEN TRUE ELSE FALSE END AS r...
                       ^
ERROR:  syntax error at or near "ELSE"
行 1: ELSE
      ^
ERROR:  syntax error at or near "IF"
行 1: END IF;
          ^