sqlpostgresqldatabase-designplpgsqlsql-delete

How to programmatically check if row is deletable?

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
);

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.

Solution

  • 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. The manual:

    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.

    Bold emphasis mine.

    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;

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

    db<>fiddle here
    Old sqlfiddle

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

    Since PostgreSQL 9.2 you can also report back which table was blocking.
    PostgreSQL 9.3 or later offer more detailed information, yet.

    Generic function for arbitrary table, column and type

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

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

    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;

    db<>fiddle here
    Old 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. See:

    I also use format() and a parameter of type regclass to safeguard against SQLi. See: