如何以编程方式检查行是否可删除? [英] How to programmatically check if row is deletable?

问题描述

假设我们有一个PostgreSQL表，如下所示:

Say we have a PostgreSQL table like so:

CREATE TABLE master (
    id INT PRIMARY KEY,
    ...
);

以及许多其他使用外键引用它的表:

and many other tables referencing it with foreign keys:

CREATE TABLE other (
    id INT PRIMARY KEY,
    id_master INT NOT NULL,
    ...
    CONSTRAINT other_id_master_fkey FOREIGN KEY (id_master)
                                    REFERENCES master (id) ON DELETE RESTRICT
);

是否有一种方法(从触发器功能内)检查主行是否可删除而不实际尝试删除它?一种明显的方法是对所有引用表一个一个地执行SELECT，但是我想知道是否有更简单的方法.

Is there a way to check (from within trigger function) if a master row is deletable without actually trying to delete it? The obvious way is to do a SELECT on all referencing tables one by one, but I would like to know if there is an easier way.

原因 我需要的是我有一个带有层次结构数据的表，其中任何行都可以有子行，而其他表只能引用层次结构中最低的子行.因此，当某行即将成为父行时，我需要检查它是否已在任何地方被引用.如果是这样，它就不能成为父行，并且拒绝插入新的子行.

The reason I need this is that I have a table with hierarchical data in which any row can have child rows, and only child rows that are lowest in hierarchy can be referenced by other tables. So when a row is about to become a parent row, I need to check whether it is already referenced anywhere. If it is, it cannot become a parent row, and insertion of new child row is denied.

推荐答案

您可以尝试删除该行并回滚效果.您不希望在触发器函数中执行此操作，因为任何异常都会取消对数据库的所有持久更改.考虑一下手册中的这句话:

You can try to delete the row and roll back the effects. You wouldn't want to do that in a trigger function because any exception cancels all persisted changes to the database. Consider this quote from the manual:

当EXCEPTION子句捕获错误时，的局部变量为 PL/pgSQL函数保持在发生错误时的状态，但是 回滚该块中对持久数据库状态的所有更改.

When an error is caught by an EXCEPTION clause, the local variables of the PL/pgSQL function remain as they were when the error occurred, but all changes to persistent database state within the block are rolled back.

粗体强调重点.

但是您可以将其包装到单独的块或单独的 plpgsql函数中，并在此捕获异常以防止对主(触发)函数产生影响.

But you can wrap this into a separate block or a separate plpgsql function and catch the exception there to prevent the effect on the main (trigger) function.

CREATE OR REPLACE FUNCTION f_can_del(_id int)
  RETURNS boolean AS 
$func$
BEGIN
   DELETE FROM master WHERE master_id = _id; -- DELETE is always rolled back

   IF NOT FOUND THEN
      RETURN NULL;                        -- ID not found, return NULL
   END IF;

   RAISE SQLSTATE 'MYERR';                -- If DELETE, raise custom exception

   EXCEPTION
   WHEN FOREIGN_KEY_VIOLATION THEN
      RETURN FALSE;
   WHEN SQLSTATE 'MYERR' THEN
      RETURN TRUE;
   -- other exceptions are propagated as usual
END  
$func$ LANGUAGE plpgsql;

这将返回TRUE/FALSE/NULL表示该行可以删除/不能删除/不存在.

This returns TRUE / FALSE / NULL indicating that the row can be deleted / not be deleted / does not exist.

-> SQLfiddle演示

一个人可以轻松地使此函数动态化以测试任何表/列/值.

One could easily make this function dynamic to test any table / column / value.

在 PostgreSQL 9.2 您还可以报告哪个表被阻止.
当前PostgreSQL 9.3 提供了更详细的信息.

In PostgreSQL 9.2 you could also report back which table was blocking.
The current PostgreSQL 9.3 offers even more detailed information.

为什么您在评论中发布的对动态函数的尝试失败了 ? 手册中的引言应该给出一个线索:

Why did the attempt on a dynamic function that you posted in the comments fail? This quote from the manual should give a clue:

尤其要注意，EXECUTE会更改GET DIAGNOSTICS的输出， 但不会更改FOUND.

Note in particular that EXECUTE changes the output of GET DIAGNOSTICS, but does not change FOUND.

它与 GET DIAGNOSTICS 一起使用:

It works with GET DIAGNOSTICS:

CREATE OR REPLACE FUNCTION f_can_del(_tbl regclass, _col text, _id int)
  RETURNS boolean AS 
$func$
DECLARE
   _ct int;                              -- to receive count of deleted rows
BEGIN
   EXECUTE format('DELETE FROM %s WHERE %I = $1', _tbl, _col)
      USING _id;                         -- exception if other rows depend

   GET DIAGNOSTICS _ct = ROW_COUNT;

   IF _ct > 0 THEN
      RAISE SQLSTATE 'MYERR';            -- If DELETE, raise custom exception
   ELSE
      RETURN NULL;                       -- ID not found, return NULL
   END IF;

   EXCEPTION
   WHEN FOREIGN_KEY_VIOLATION THEN
      RETURN FALSE;
   WHEN SQLSTATE 'MYERR' THEN
      RETURN TRUE;
   -- other exceptions are propagated as usual
END  
$func$ LANGUAGE plpgsql;

> -> SQLfiddle演示

在使用它的同时，我将其设置为完全动态，包括列的数据类型(当然，它必须与给定的列匹配).我正在使用多态类型 .此相关答案中的更多说明:
如何编写返回文本还是整数值?

While being at it, I made it completely dynamic, including the data type of the column (it has to match the given column, of course). I am using the polymorphic type anyelement for that purpose. more explanation in this related answer:
How to write a function that returns text or integer values?

我还使用format()和类型为regclass的参数来防范SQLi. 有关dba.SE 的详细说明.

I also use format() and a parameter of type regclass to safeguard against SQLi. Detailed explanation in this related answer on dba.SE.

这篇关于如何以编程方式检查行是否可删除?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！