March 1st, 2007 by depesz | | 4 comments »
Did it help? If yes - maybe you can help me?

a dziś mam dla państwa zagadkę.

zagadka powstała wczoraj w firmie, i aż się zdziwiłem, że okazała się taka skuteczna 🙂

zadanie jest proste. mamy tabelkę:

create table posts (id serial primary key, paired_with int references posts (id), title text, body text);

inne kolumny są nieistotne.

i teraz – post może być sparowany lub nie. jak nie jest – w paired_with ma null'a.

jak jest – to ma tam id innego postu.

nie można być sparowanym samym z sobą.

i teraz – chcemy stworzyć trigger lub triggery które utrzymają tam porządek:

  • nie dopuszczą do parowania z samym sobą
  • pilnują by wszystkie relacje były poprawne. czyli jeśli post 1 jest sparowany z 2 to 2 musi być z 1.
  • posty mogą być wstawiane sparowane lub nie
  • posty mogą być w dowolnej chwili update'owane.

proste? pewnie, że proste.

jak masz chwilę – napisz tego triggera(y)

jako ciekawostkę mogę podać, że znajomy tworzył wczoraj coś takiego przez kilka godzin 🙂

aha – ów znajomy proszony jest o nie podsyłanie rozwiązania.

  1. 4 comments

  2. Mar 1, 2007

    A nie lepiej byłoby zrobić osobną tabelę z parami i ewentualny widok o strukturze jaką potrzebujecie, plus reguły na widoku?

  3. Mar 1, 2007

    ja wiem jak to zrobić. oddzielna tabelka ma plusy i minusy i celem zagadki nie jest wymyślenie lepszej strutkruy.
    celem zagadki jest napisanie triggera.

    depesz

  4. # Maniek
    Mar 1, 2007

    O to chodziło:

    create or replace function f() returns trigger language ‘plpgsql’ volatile as
    $_$
    DECLARE
    BEGIN
    if NEW.id = NEW.paired_with then
    RAISE exception ‘paired with itself’;
    END IF;
    RAISE INFO ‘TG_OP: %’,tg_op;

    if tg_op = ‘INSERT’ then
    raise info ‘insert’;
    update posts set paired_with=NEW.id where id=NEW.paired_with and paired_with is distinct from NEW.id;
    elsif tg_op=’UPDATE’ then
    raise info ‘update id=% paired_with=%’,NEW.id,NEW.paired_with;
    if NEW.paired_with is distinct from OLD.paired_with then
    if OLD.paired_with is not null then
    update posts set paired_with=NULL where id=OLD.paired_with and paired_with is not null;
    if NEW.paired_with OLD.paired_with then
    update posts set paired_with=NEW.id where ID=NEW.paired_with and paired_with is distinct from NEW.id;
    end if;
    else
    update posts set paired_with=NEW.id where id=NEW.paired_with and paired_with is distinct from NEW.id;
    end if;
    end if;
    end if;
    return new;
    END
    $_$;

    create trigger t after insert or update on posts for each row execute procedure f();

    ? Nie próbowałem analizować, jak się zachowa w momencie, kiedy kilka transakcji spróbuje równocześnie zmieniać te same dane.

  5. # noHuman
    Jun 30, 2007

    sorry za forme ale wkleje to tak jak mam w pgAdminie

    — Function: posts_before()

    — DROP FUNCTION posts_before();

    CREATE OR REPLACE FUNCTION posts_before()
    RETURNS “trigger” AS
    $BODY$DECLARE
    tmp int;
    BEGIN

    — nie dopuszczą do parowania z samym sobą
    IF NEW.id=NEW.paired_with THEN
    RETURN NULL;
    END IF;

    tmp=(SELECT paired_with FROM posts WHERE id=NEW.paired_with);

    — jeśłi niema rekordu z którym prubujemy być zparowani to odrzucamy całą operacje
    IF NEW.paired_with is not NULL AND (SELECT count(*) FROM posts WHERE id=NEW.paired_with)=0 THEN
    RETURN NULL;
    END IF;

    — post paired_with mode być sparowany z NEW.id albo z NULL
    IF tmp is not NULL AND tmpNEW.id THEN
    — jeśłi niejest potencjalny partner sparowany z napi albo z NULL to odrzycamy całą poprawkę
    RETURN NULL;
    END IF;
    — Jeśli jest nasz partner ma NULL to poprawimy to w AFTER ;)))

    RETURN NEW;
    END;$BODY$
    LANGUAGE ‘plpgsql’ VOLATILE;
    ALTER FUNCTION posts_before() OWNER TO postgres;

    — Function: posts_after()

    — DROP FUNCTION posts_after();

    CREATE OR REPLACE FUNCTION posts_after()
    RETURNS “trigger” AS
    $BODY$DECLARE tmp int;
    BEGIN

    — jełśi nasz partner jest NULL to my to własnie zmieniamy
    IF (SELECT paired_with FROM posts WHERE id=NEW.paired_with) is NULL THEN
    UPDATE posts SET paired_with=NEW.id WHERE id=NEW.paired_with;
    END IF;

    RETURN NEW;
    END;$BODY$
    LANGUAGE ‘plpgsql’ VOLATILE;
    ALTER FUNCTION posts_after() OWNER TO postgres;

    — Function: posts_afret_update()

    — DROP FUNCTION posts_afret_update();

    CREATE OR REPLACE FUNCTION posts_afret_update()
    RETURNS “trigger” AS
    $BODY$BEGIN
    — Tu Jest OLD więc może ten trigger być wwywołany tylko w update
    — pewnie można to jakoś sprawdzić z czego jest wołany ale jeszcze nieumiem

    — JEśli rekord zmienił partnera to poprzedni musi mieć ustawionego partnera na NULL
    IF NEW.paired_with OLD.paired_with OR NEW.paired_with is NULL THEN
    UPDATE posts SET paired_with=NULL WHERE id=OLD.paired_with;
    END IF;

    RETURN NEW;
    END;$BODY$
    LANGUAGE ‘plpgsql’ VOLATILE;
    ALTER FUNCTION posts_afret_update() OWNER TO postgres;

    posts_after i posts_before (czas wykonania jak w nazwie) są z INSERT i UPDATE a posts_after_update tylo z after, update

    jeszcze sobie dla pewności dałem kliusz unikalny na kolumnę paired_with tak dla pewności (dysleksja i durzo błęów robie ; )

Leave a comment