findFieldsReferencedTablesSQL static method

@visibleForTesting String findFieldsReferencedTablesSQL(String table)

The SQL of findFieldsReferencedTablesImpl: the foreign keys of table (escaped as a string literal, see DBSQLAdapter.sqlStringLiteral).

Implementation

@visibleForTesting
static String findFieldsReferencedTablesSQL(String table) =>
    '''
  SELECT
    o.conname AS constraint_name,

    m.relname AS source_table,
    stc_attr.attname AS source_column,
    src_inf.data_type AS source_column_type,

    f.relname AS target_table,
    targ_attr.attname AS target_column,
    targ_inf.data_type AS target_column_type,

    idx.relname AS fk_index_name

  FROM
    pg_constraint o
    LEFT JOIN pg_class f ON f.oid = o.confrelid
    LEFT JOIN pg_class m ON m.oid = o.conrelid

  INNER JOIN pg_attribute stc_attr
    ON stc_attr.attrelid = m.oid
    AND stc_attr.attnum = o.conkey[1]
    AND stc_attr.attisdropped = false

  INNER JOIN information_schema.columns src_inf
    ON src_inf.table_name = m.relname
    AND src_inf.column_name = stc_attr.attname

  INNER JOIN pg_attribute targ_attr
    ON targ_attr.attrelid = f.oid
    AND targ_attr.attnum = o.confkey[1]
    AND targ_attr.attisdropped = false

  INNER JOIN information_schema.columns targ_inf
    ON targ_inf.table_name = f.relname
    AND targ_inf.column_name = targ_attr.attname

	LEFT JOIN LATERAL (
    SELECT
      i.indexrelid
    FROM pg_index i
    WHERE i.indrelid = m.oid
      -- FK column must be the *first* column of the index. (i.indkey is 0-based)
      AND i.indkey[0] = o.conkey[1]
    ORDER BY
      array_length(i.indkey, 1) ASC,  -- single-column first
      i.indisunique DESC               -- prefer unique
    LIMIT 1
  ) idx_def ON true

  LEFT JOIN pg_class idx
    ON idx.oid = idx_def.indexrelid

  WHERE
    o.contype = 'f'
    AND m.relname = ${DBSQLAdapter.sqlStringLiteral(table)}
    AND o.conrelid IN (
      SELECT oid FROM pg_class c WHERE c.relkind = 'r'
    );
  ''';