Блог

В Postgres я пытаюсь наследовать уникальный атрибут от родительского элемента к его дочерним элементам. Родительская таблица является абстрактной таблицей, в которой нет записей. Имена всех дочерних элементов должны быть уникальными. Ссылаясь на следующий маленький (глупый) пример: не должно быть яблока с таким же именем, как у банана (или, конечно, другого яблока).

Минималистичный пример Postgres для этого сценария:

 CREATE TABLE fruit(CONSTRAINT fruit_uniq_name UNIQUE (name))
CREATE TABLE banana(name text, length integer) INHERITS (fruit)
CREATE TABLE apple(name, diameter integer NOT NULL,) INHERITS (fruit)
  

После прочтения многих сообщений об этой проблеме. Все они пришли к выводу, что этот сценарий невозможно освоить только с наследованием Postgres, я хотел бы знать, существует ли обходной путь наилучшей практики, например, с использованием триггеров и функций, для этой проблемы?

Я был бы очень рад каждому небольшому фрагменту кода, который мог бы помочь мне выбраться из этой досадной ловушки.

Я последовал совету Лоренца Альбе и, думаю, решил проблему, используя триггеры в таблицах apple и banana и триггерную функцию tgf_name_exists() , которая проверяет уникальность.

Это триггерная функция, которая проверяет уникальность имен дочерних элементов:

 CREATE OR REPLACE FUNCTION tgf_name_exits()
    RETURNS trigger
    LANGUAGE 'plpgsql'
    VOLATILE
    COST 100
AS $BODY$
    declare
    count_apple integer;
    count_banana integer;
    name text;
    schema text;
    error_text text;
BEGIN
    -- Variables
    error_text = '';
    schema = TG_TABLE_SCHEMA; -- actual schema
    name = NEW.name; --- actual attribute name

    -- Check
    EXECUTE format('SELECT count(*) FROM %s.apple apl WHERE apl.name=%L', schema, name) INTO count_apple;
    EXECUTE format('SELECT count(*) FROM %s.banana ban WHERE ban.name=%L', schema, name) INTO count_banana;

    -- Info 
    RAISE NOTICE 'Schema: %', schema;
    RAISE NOTICE 'Name: %', name;
    RAISE NOTICE 'Count: %', count_apple;
    RAISE NOTICE 'Count: %', count_banana;

    IF count_apple > 0 OR count_banana > 0 THEN
        -- Name ist already used
        if count_apple > 0 then
            error_text = error_text || "apple "
        end if;
        if count_banana > 0 then
            error_text = error_text || "banana "
        end if;
        RAISE EXCEPTION 'Name % already existing in table %', name, error_text;
    ELSE
        -- Name is unused -> OK 
        RETURN NEW;
    END IF;
END;
$BODY$;
  

Это триггеры для таблиц apple и banana

 CREATE TRIGGER tg_apple_name_instert_update
    BEFORE INSERT OR UPDATE 
    ON apple
    FOR EACH ROW
    EXECUTE PROCEDURE tgf_name_exits();

CREATE TRIGGER tg_banana_name_uniq
    BEFORE INSERT OR UPDATE 
    ON banana
    FOR EACH ROW
    EXECUTE PROCEDURE tgf_name_exits();
  

Было бы очень любезно, если бы кто-нибудь мог это проверить. Отсюда похоже, что это работает.

возможно, я думаю об этом слишком упрощенно, но, похоже, эту функциональность можно воссоздать следующим образом :

 CREATE FUNCTION fruit_check ( _name_ text )
RETURNS
boolean AS
        $$ SELECT _name_ NOT IN ( SELECT name FROM fruit )
        $$
        STABLE LANGUAGE SQL ;

ALTER TABLE fruit ADD CHECK ( fruit_check ( name ) ) ;

CREATE TABLE banana ( LIKE fruit INCLUDING ALL , length int            ) INHERITS ( fruit ) ;
CREATE TABLE  apple ( LIKE fruit INCLUDING ALL , diameter int NOT NULL ) INHERITS ( fruit ) ;

-- now test ...

  INSERT INTO banana VALUES ( 'ripe'  , 21 ) ; -- INSERT 0 1
  INSERT INTO apple  VALUES ( 'rosie' , 21 ) ; -- INSERT 0 1

  INSERT INTO banana VALUES ( 'rosie' , 21 ) ;
--
--  ERROR:  new row for relation "banana" violates check constraint "fruit_name_check"

  INSERT INTO apple  VALUES ( 'ripe'  , 21 ) ;
--
--  ERROR:  new row for relation "apple" violates check constraint "fruit_name_check"

  UPDATE apple set name = 'ripe' where name = 'rosie' ;
--
-- ERROR:  new row for relation "apple" violates check constraint "fruit_name_check"