主页 / dba / 问题 / 205684
Accepted
guettli
Asked: 2018-05-04 02:23:48 +0800 CST

选择孤立的行（以通用方式）

  • 772

我有一个表my_tables，它在几个表中被引用为外键。

我想选择my_table其他表中未引用的所有行。

AFAIK，应该可以用一种通用的方式来做到这一点（有一些内省的魔法）。

postgresql foreign-key

2 个回答

  1. Best Answer

    引用手册：

    外键必须引用作为主键或形成唯一约束的列。

    所以这不一定限于PK。但是如果我们从 开始pg_constraint，我们会自动获得所有指向目标表的 FK 约束。无需提供任何键列 - 除非您想限制某些 FK。

    使用对象标识符类型regclass和表别名，我们可以保持函数简短，结果安全且明确：

    基本查询

    SELECT format(E'SELECT * FROM %s t\nWHERE  NOT EXISTS (SELECT FROM %s'
            , c.confrelid::regclass
            , string_agg(format('%s WHERE %s = %s)', c.conrelid::regclass, src.cols, tgt.cols)
                       , E'\nAND    NOT EXISTS (SELECT FROM '))
FROM   pg_constraint c
     , cardinality(c.conkey) AS col_ct
     , LATERAL (
   SELECT concat(CASE WHEN col_ct > 1 THEN '(' END
               , string_agg(quote_ident(attname), ', ' ORDER BY fld.ord) -- original order
               , CASE WHEN col_ct > 1 THEN ')' END) AS cols
   FROM   unnest(c.conkey) WITH ORDINALITY fld(attnum, ord)             -- possibly n cols
   JOIN   pg_catalog.pg_attribute a ON (a.attrelid, a.attnum) = (c.conrelid, fld.attnum)
   ) src
     , LATERAL (
   SELECT concat(CASE WHEN col_ct > 1 THEN '(' END     -- parentheses for multiple columns
               , string_agg('t.' || quote_ident(attname), ', t.' ORDER BY fld.ord)
               , CASE WHEN col_ct > 1 THEN ')' END) AS cols
   FROM   unnest(c.confkey) WITH ORDINALITY fld(attnum, ord)
   JOIN   pg_catalog.pg_attribute a ON (a.attrelid, a.attnum) = (c.confrelid, fld.attnum)
   ) tgt
WHERE  c.confrelid = 'my_table'::regclass -- target table name, optionally schema-qualified
AND    c.contype = 'f'  -- FK constraints
GROUP  BY c.confrelid;

    产生以下形式的查询：

    SELECT * FROM my_table t
WHERE  NOT EXISTS (SELECT FROM schema1.tbl1 WHERE col1 = t.id)
AND    NOT EXISTS (SELECT FROM "tB-l2" WHERE ("COL2", col3) = (t.col4, t.col5));

    它返回当前未被任何FK 约束引用的所有行。

    如果cardinality(c.conkey) > 1那么它也是安全的假设cardinality(c.confkey) > 1。所以只计算一次来决定是否加括号。

    全自动化

    要动态地对任何输入表进行此操作，请创建一个采用表的行值的多态函数：

    CREATE OR REPLACE FUNCTION f_orphans(_tbl anyelement)
  RETURNS SETOF anyelement AS
$func$
BEGIN

RETURN QUERY EXECUTE (  -- exactly the query from above
SELECT format(E'SELECT * FROM %s t\nWHERE  NOT EXISTS (SELECT FROM %s'
            , c.confrelid::regclass
            , string_agg(format('%s WHERE %s = %s)', c.conrelid::regclass, src.cols, tgt.cols)
                       , E'\nAND    NOT EXISTS (SELECT FROM '))
FROM   pg_constraint c
     , cardinality(c.conkey) AS col_ct
     , LATERAL (
   SELECT concat(CASE WHEN col_ct > 1 THEN '(' END
               , string_agg(quote_ident(attname), ', ' ORDER BY fld.ord)
               , CASE WHEN col_ct > 1 THEN ')' END) AS cols
   FROM   unnest(c.conkey) WITH ORDINALITY fld(attnum, ord)
   JOIN   pg_catalog.pg_attribute a ON (a.attrelid, a.attnum) = (c.conrelid, fld.attnum)
   ) src
     , LATERAL (
   SELECT concat(CASE WHEN col_ct > 1 THEN '(' END
               , string_agg('t.' || quote_ident(attname), ', t.' ORDER BY fld.ord)
               , CASE WHEN col_ct > 1 THEN ')' END) AS cols
   FROM   unnest(c.confkey) WITH ORDINALITY fld(attnum, ord)
   JOIN   pg_catalog.pg_attribute a ON (a.attrelid, a.attnum) = (c.confrelid, fld.attnum)
   ) tgt
WHERE  c.confrelid = pg_typeof(_tbl)::text::regclass  -- input goes here!
AND    c.contype = 'f'
GROUP  BY c.confrelid
);

END
$func$  LANGUAGE plpgsql;

    打电话（重要）！

    SELECT * FROM f_orphans(NULL::my_table);

    或者：

    SELECT * FROM f_orphans(NULL::myschema.my_table);

    有关的：

    • 7

  2. 这是一个示例查询，可让您创建 sql 语句：

    WITH 
  fkey_fields AS (
    SELECT DISTINCT
        nt.nspname AS table_schema,
        t.relname AS table_name,
        unnest(c.conkey) AS field_index,
        nft.nspname AS foreign_table_schema,
        ft.relname AS foreign_table_name,
        unnest(c.confkey) AS foreign_field_index
      FROM pg_constraint c
      JOIN pg_class t ON t.oid = c.conrelid
      JOIN pg_namespace nt ON nt.oid = t.relnamespace
      JOIN pg_class ft ON ft.oid = c.confrelid
      JOIN pg_namespace nft ON nft.oid = ft.relnamespace
      WHERE
        c.contype = 'f'
  ),
  table_fields AS (
    SELECT
        rn.nspname AS table_schema,
        c.relname AS table_name,
        a.attname AS field_name,
        a.attnum AS field_index
      FROM pg_attribute a
      JOIN pg_class c ON a.attrelid = c.oid
      JOIN pg_namespace rn ON c.relnamespace = rn.oid
      WHERE 
        a.attnum > 0 AND
        a.attisdropped <> 't' 
  )
SELECT
  concat(
    format(
      'SELECT * FROM %I.%I',
      fkf.foreign_table_schema,
      fkf.foreign_table_name
    ),

    E'\nWHERE\n' ||
    string_agg(
      format('NOT EXISTS (SELECT 1 FROM %I.%I where %I.%I.%I = %I.%I.%I)',
        fkf.table_schema,
        fkf.table_name,
        fkf.table_schema,
        fkf.table_name,
        tf.field_name,    
        fkf.foreign_table_schema,
        fkf.foreign_table_name,
        foreign_tf.field_name  
      ),
      E' AND\n'
    )
  )
  FROM fkey_fields fkf
  JOIN table_fields tf ON
    tf.table_schema = fkf.table_schema AND
    tf.table_name = fkf.table_name AND 
    tf.field_index = fkf.field_index
  JOIN table_fields foreign_tf ON
    foreign_tf.table_schema = fkf.foreign_table_schema AND
    foreign_tf.table_name = fkf.foreign_table_name AND 
    foreign_tf.field_index = fkf.foreign_field_index
  WHERE
    fkf.foreign_table_schema = 'public' AND 
    fkf.foreign_table_name = 'my_table'
  GROUP BY
    fkf.foreign_table_schema,
    fkf.foreign_table_name;

    输出示例如下；

    SELECT * FROM public.my_table
WHERE
NOT EXISTS (SELECT 1 FROM public.table2 where public.table2.my_table_id = public.my_table.id) AND 
NOT EXISTS (SELECT 1 FROM public.table3 where public.table3.my_table_id = public.my_table.id)

    小提琴链接在这里

    • 4

相关问题