From: | "Sterpu Victor" <victor(at)caido(dot)ro> |
---|---|
To: | "PostgreSQL General" <pgsql-general(at)postgresql(dot)org> |
Subject: | Unique values on multiple tables |
Date: | 2016-03-28 06:32:17 |
Message-ID: | em2f0f78a9-9724-4df7-ac25-701271d4e489@victor-pc |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Sterpu Victor | 2016-03-28 06:55:43 | Re: Unique values on multiple tables |
Previous Message | David G. Johnston | 2016-03-28 04:28:41 | Re: Nested funtion |