[Top][All Lists]
[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Gnumed-devel] Transaction for creating rows in link tables
From: |
Jim Busser |
Subject: |
[Gnumed-devel] Transaction for creating rows in link tables |
Date: |
Tue, 13 Sep 2011 14:18:32 -0700 |
Pending resolution of whether is_live can be NULL, the following nearly
succeeds to add vaccines where they do not already exist in clin.vaccine
INSERT INTO clin.vaccine (
is_live,
fk_brand,
id_route
)
SELECT
NULL, r_bd.pk, c_vr.id
FROM ref.branded_drug AS r_bd
INNER JOIN staging.vaccine s_v
ON s_v.fk_external_code = r_bd.external_code
INNER JOIN clin.vacc_route c_vr
ON UPPER(c_vr.description) = UPPER(s_v.route)
WHERE r_bd.fk_data_source = (SELECT pk from Ref.data_source WHERE
position('FreeDiams' in name_short) > 0)
AND NOT EXISTS
(SELECT 1
FROM clin.vaccine c_v
INNER JOIN ref.branded_drug as r_bd
ON c_v.fk_brand = r_bd.pk
WHERE r_bd.fk_data_source = (SELECT pk from
Ref.data_source WHERE position('FreeDiams' in name_short) > 0)
AND s_v.fk_external_code = r_bd.external_code
LIMIT 1
)
;
except for the constraint
ERROR: [clin.vaccine]:
INSERT/UPDATE failed: no indication linked to vaccine
(clin.lnk_vaccine2inds.fk_vaccine <-(82)-> clin.vaccine.pk)
Therefore if adding vaccines depends on (at the same time) creating links to
indications, would I wrap the above as foo1 inside
begin;
foo1 ;
foo2 ;
end; (or commit;)
followed by foo2 where foo2 inserts suitable records into
clin.lnk_vaccine2inds.fk_vaccine
??
-- Jim
- [Gnumed-devel] Transaction for creating rows in link tables,
Jim Busser <=