Re: Unique values on multiple tables

I think I fixed the problem by executing the function AFTER insert or
update but I'm not sure.
Until now the execution was before insert or update.

------ Original Message ------
From: "Sterpu Victor" <victor(at)caido(dot)ro>
To: "PostgreSQL General" <pgsql-general(at)postgresql(dot)org>
Sent: 28/3/2016 9:32:17 AM
Subject: [GENERAL] Unique values on multiple tables

>Hello
>
>I have 2 tables and I must make asure unique values like this.
>
>table1
>id
>nr - integer
>
>table2
>id
>id_table1 - FK in Table 1
>valid_from - timestamp
>
>There must be unique values for:
>- nr - from table1
>and
>- YEAR(MIN(valid_from)) from table 2
>
>I already made this with a function but in rare cases the function
>fails: when the insert time is very close for 2 inserts the check will
>fail and I will have 2 numbers on the same year.
>How should I fix this?
>
>This is the function:
>
>CREATE OR REPLACE FUNCTION table2_check_uni_func() RETURNS trigger AS
>$BODY$
>DECLARE
> currenr_nr_fo integer;
> current_id integer;
>BEGIN
> IF( (SELECT id FROM table2 WHERE id_table1=NEW.id_table1 ORDER BY
>valid_from ASC LIMIT 1) = NEW.id ) THEN /*IF the first valid from is
>edited*/
> SELECT INTO currenr_nr_fo f.nrfo FROM table1 f WHERE f.id
>= NEW.id_table1;
> IF( (SELECT count(*) as nr
> FROM table1 f
> JOIN table2 fd1 ON (fd1.id_table1 = f.id AND
>to_char(fd1.valid_from, 'YYYY')=TO_CHAR(NEW.valid_from, 'YYYY'))
> LEFT JOIN table2 fd2 ON (fd2.id_table1 = f.id AND
>fd2.valid_from<fd1.valid_from)
> WHERE f.nr = currenr_nr_fo AND f.id!=NEW.id_table1
>AND fd2.id IS NULL) > 0 ) THEN RAISE EXCEPTION 'Nr % already used',
>currenr_nr_fo ;
> ELSE
> RETURN NEW;
> END IF;
> ELSE
> RETURN NEW;
> END IF;
>END;
>
>Thank you.
>
>
>DISCLAIMER:
>Acest mesaj de posta electronica si documentele aferente sunt
>confidentiale. Este interzisa distribuirea, dezvaluirea sau orice alt
>mod de utilizare a lor. Daca nu sunteti destinatarul acestui mesaj,
>este interzis sa actionati in baza acestor informatii. Citirea,
>copierea, distribuirea, dezvaluirea sau utilizarea in alt mod a
>informatiei continute in acest mesaj constituie o incalcare a legii.
>Daca ati primit mesajul din greseala, va rugam sa il distrugeti,
>anuntand expeditorul de eroarea comisa. Intrucat nu poate fi garantat
>faptul ca posta electronica este un mod sigur si lipsit de erori de
>transmitere a informatiilor, este responsabilitatea dvs. sa va
>asigurati ca mesajul (inclusiv documentele alaturate lui) este validat
>si autorizat spre a fi utilizat in mediul dvs.
>
>