postusuarioemail(integer, character varying, character varying, integer, character varying)

declare 

    internalid integer;

begin   

    --EMAIL NULO

    if email is null then 

        RAISE EXCEPTION 'email = %', email

            USING HINT = 'Field email must be not null';

     end if;

    --IDNPADRAO NULO OU FORA DO PADRAO 

    if idnpadrao is null or idnpadrao not in (0,1) then 

        RAISE EXCEPTION 'idnpadrao = %', idnpadrao

            USING HINT = 'Field must not be null or different from 1 or 0.';

     end if;  

​

    --LOOKUP IDUSUARIO

    IF PostUsuarioEmail.idusuario IS NULL AND PostUsuarioEmail.idusuario_externalid IS NOT NULL

        THEN PostUsuarioEmail.idusuario = (SELECT C2.idusuario FROM usuario C2 WHERE C2.CODIGOERP = PostUsuarioEmail.idusuario_externalid);

    END IF; 

​

     --IDUSUARIO NULO

     if PostUsuarioEmail.idusuario is null then 

        RAISE EXCEPTION 'idusuario = %', PostUsuarioEmail.idusuario

            USING HINT = 'Field idusuario must be not null';

     end if;   

​

     --CODIGOERP NULO

     if externalid is null or externalid = '' then 

        RAISE EXCEPTION 'externalid = %', externalid

            USING HINT = 'Field externalid must be not null';

     end if;

​

    --VALIDAÇÃO DE CODIGOERP JÁ EXISTENTE

    IF (select distinct 1 FROM USUARIOEMAIL WHERE CODIGOERP = PostUsuarioEmail.externalid) = 1

        THEN RAISE EXCEPTION 'externalid = %', PostUsuarioEmail.externalid 

                USING HINT = 'The record (externalid = '|| PostUsuarioEmail.externalid ||') already exists, it was not possible to insert it.';

        ELSE insert into usuarioemail (idusuarioemail,email,idnpadrao,idusuario,codigoerp) values (nextval('seqpkusuarioemail'),email,idnpadrao,idusuario,externalid) returning idusuarioemail into internalid;

     end if;

    return query select internalid;

END;