Re: [Gnumed-devel] import patient data - basic procedure

[Karsten Hilbert]

On Sun, Apr 28, 2013 at 08:01:10PM +0200, Jerzy Luszawski wrote:

> CREATE OR REPLACE FUNCTION dem.add_person(_lastname text, _firstname text, 
> _gender text)
>       -- you may prefer to change the arguments to add day of birth instead 
> of gender
>   RETURNS integer AS -- returns dem.identity.pk of newly created person
> $BODY$
> DECLARE
>       _id integer;
> BEGIN
>       -- name already there ?
>       select into _id id from dem.names where firstnames = _firstname and 
> lastnames = _lastname;
>       if FOUND then
>               RAISE EXCEPTION 'This name already exist.'
>               USING HINT = 'Name ID: ' || _id::text ;
>       end if;

This is not correct. Names are unique only with respect to a
given identity. Different identities may well have the same
name and those names are NOT normalized (as opposed to many
other things) into one row (because one may need to edit the
name of a person and we DON'T want other people to acquire a
different name by magic).

Given the above you can simply *always* do this:

>       -- no, insert new identity and name
>       INSERT INTO  dem.identity (gender) VALUES(_gender);
>       INSERT INTO dem.names (id_identity, firstnames, lastnames) VALUES 
> (currval('dem.identity_pk_seq'), _firstname, _lastname);

unless you want to check whether the *patient* (identity +
name) already exists in the database and you want to try to
avoid duplicates.

>       if FOUND then
>               return currval('dem.names_id_seq');
>       end if;

This would return the PK of the dem.names record, contrary
to what your comment assures:

>   RETURNS integer AS -- returns dem.identity.pk of newly created person

You'd have to return dem.identity_pk_seq.

>       return NULL;
> END;$BODY$
>   LANGUAGE plpgsql VOLATILE
>   COST 100;
> ALTER FUNCTION dem.add_person(text, text, text)
>   OWNER TO "gm-dbo";

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