Re: [Gnumed-devel] Transaction for creating rows in link tables

[Karsten Hilbert]

On Tue, Sep 13, 2011 at 02:18:32PM -0700, Jim Busser wrote:

> 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
> 
> ??

Indeed. Note that it does not matter whether:

        begin;
        foo1.1;
        foo1.2;
        foo1.3;

        foo2.1;
        foo2.2;
        foo2.3;
        end;

or:

        begin;
        foo1.1;
        foo2.1;

        foo1.2;
        foo2.2;

        foo1.3;
        foo2.3;
        end;

Karsten
-- 
GPG key ID E4071346 @ gpg-keyserver.de
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346