2

I guess not but if someone has an idea perhaps could give me a hand.

I'm trying to restrict the deletion of a row of a table under some circumstances that requires to query against other tables.

An example

Each worker (table worker) references the physical person (table person). The check aims to allows the existence of workers that will work with his actual_hapiness_level (from table worker) >= base_hapiness_level (from table person), so all the underlying persons are happy.

There is an important exception, is you hire a worker that acts a clown, everybody is happy and therefore the former restrictions doesn't apply. Try to follow the example running one statement at the time, reading the comments.

And the end you will get inconsesty from a "business" point of view.

create table person (
    id serial primary key,
    base_hapiness_level int,
    name text
);
create table worker (
    id serial primary key,
    person_id int,
    actual_hapiness_level int,
    acts_as_clown bool default false,
    CONSTRAINT person_id_fkey FOREIGN KEY (person_id)
          REFERENCES person (id) MATCH SIMPLE
          ON UPDATE NO ACTION ON DELETE NO ACTION
);

CREATE OR REPLACE FUNCTION is_worker_happy_according_to_his_personal_values (bigint,bigint) RETURNS boolean AS $BODY$
DECLARE
    BEGIN
    RAISE NOTICE 'Performing things_are_okey(person_id= %,base_hapiness_level= %)', $1, $2;
    RETURN ($2 >= (select p.base_hapiness_level from person p where p.id = $1));
    END;
$BODY$ LANGUAGE plpgsql IMMUTABLE;

CREATE OR REPLACE FUNCTION is_there_a_clown_in_there_to_handle_the_unhappiness () RETURNS boolean AS $BODY$
DECLARE
    BEGIN
    RAISE NOTICE 'Performing is_there_a_clown_in_there_to_handle_the_unhappiness';
    RETURN EXISTS (select * from worker where acts_as_clown is true);
    END;
$BODY$ LANGUAGE plpgsql IMMUTABLE;

ALTER TABLE worker ADD CONSTRAINT person_can_work check (
    is_worker_happy_according_to_his_personal_values(person_id,actual_hapiness_level) OR
    (NOT is_worker_happy_according_to_his_personal_values(person_id,actual_hapiness_level) AND is_there_a_clown_in_there_to_handle_the_unhappiness())
);

-- create 3 persons
insert into person (base_hapiness_level,name) values (50,'robert');
insert into person (base_hapiness_level,name) values (10,'cris');
insert into person (base_hapiness_level,name) values (0,'the_clown');

-- insertion 1 is okey, as there is no clow required to the hapiness level of robert
insert into worker (actual_hapiness_level,person_id) values (50,(select id from person where name = 'robert'));

-- insertion 2 goes wrong, as there is no clown to handle the unhapiness of crish
insert into worker (actual_hapiness_level,person_id) values (1,(select id from person where name = 'cris'));

-- let's hire a clown.....
insert into worker (actual_hapiness_level,person_id,acts_as_clown) values (100,(select id from person where name = 'the_clown'),true);

-- try to insert cris again (with the clown is alright)
insert into worker (actual_hapiness_level,person_id) values (1,(select id from person where name = 'cris'));
-- and works ;)

-- but if we kick the clown, now there is no one to handle the unhapinness what a mess!! what about the check :(!!!
delete from worker where person_id = (select id from person where name = 'the_clown')

As you can realize, the check is not beign performing on the deletion of the clown and that is a problem in this example domain model

  • Please add a minimal example. – McNets Mar 15 '18 at 20:47
  • @McNets done, try to bear with me.... – Victor Mar 15 '18 at 21:44
3

Have you tried ON DELETE RESTRICT ? You can combine this constraint with a function attached to the foreign tables CREATE TRIGGER ON DELETE

CREATE TABLE my_table (
  foreign_key_id uuid REFERENCES my_foreign_table ON DELETE RESTRICT
);

CREATE OR REPLACE FUNCTION foreign_object_delete()
RETURNS TRIGGER AS $$
BEGIN
    DELETE FROM my_table 
    WHERE foreign_key_id IS NULL 
    AND foreign_key_id = OLD.foreign_key_id;
    RETURN OLD;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER foreign_row_trigger BEFORE DELETE ON my_foreign_table 
    FOR EACH ROW EXECUTE PROCEDURE foreign_object_delete();

Update: Check a condition in several tables before deleting the record

CREATE OR REPLACE FUNCTION foreign_object_delete()
RETURNS TRIGGER AS $$
BEGIN
   IF SELECT exists(SELECT 1 FROM table_x WHERE column_x = 'SAMPLE') THEN
      DELETE FROM my_table 
      WHERE foreign_key_id IS NULL 
      AND foreign_key_id = OLD.foreign_key_id;
   END IF;
      RETURN OLD;
END;
$$ LANGUAGE plpgsql;
  • Thanks, but i guess that is not my case... i need to restrict deletion of row under some circunstamces that are not limited to the table itself. – Victor Mar 15 '18 at 21:49
  • Through the function you can access any table you want and establish your custom condition check, the function scope is not closed to the table that is attached to with the trigger. @Victor – Imanol Y. Mar 15 '18 at 21:53
  • please @Imanol .Y can you show me an example where you actually stop the deletion when some circustantes are not beign fullfilled, there is no reference to a foreign table in my case.... – Victor Mar 15 '18 at 21:58
  • @Victor Just a random query to apply a condition check before deleting a row. You can use any condition or delete statement you want cross-tables. – Imanol Y. Mar 15 '18 at 22:06
  • 1
    finally get it, peeking at postgresql.org/docs/9.1/static/plpgsql-trigger.html, the part it says ' In the case of a before-trigger on DELETE, the returned value has no direct effect, but it has to be nonnull to allow the trigger action to proceed. Note that NEW is null in DELETE triggers, so returning that is usually not sensible. The usual idiom in DELETE triggers is to return OLD.' ... it would be nice (as home assignment :P) to adapt this answer to my model anyway... for now let's mark is a correct. – Victor Mar 16 '18 at 14:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.