aonestar.public

Parameters

Name	Type	Mode

Definition

DECLARE
    v_country_id integer;
    v_existing_id integer;
BEGIN
    IF TG_OP = 'UPDATE'
       AND ROW(NEW.doc_type,
               fn_normalize_guest_document_number(NEW.doc_number::text),
               fn_guest_document_country(NEW.doc_country, NEW.country_id))
           IS NOT DISTINCT FROM
           ROW(OLD.doc_type,
               fn_normalize_guest_document_number(OLD.doc_number::text),
               fn_guest_document_country(OLD.doc_country, OLD.country_id))
    THEN
        RETURN NEW;
    END IF;

v_country_id := fn_guest_document_country(NEW.doc_country, NEW.country_id);
    IF NEW.doc_type IS NULL
       OR fn_normalize_guest_document_number(NEW.doc_number::text) IS NULL
       OR v_country_id IS NULL
    THEN
        RETURN NEW;
    END IF;

PERFORM pg_advisory_xact_lock(hashtextextended(
        NEW.doc_type::text || ':'
        || fn_normalize_guest_document_number(NEW.doc_number::text) || ':'
        || v_country_id::text,
        0
    ));

SELECT g.id INTO v_existing_id
    FROM guest g
    WHERE g.id <> COALESCE(NEW.id, 0)
      AND g.doc_type = NEW.doc_type
      AND fn_normalize_guest_document_number(g.doc_number::text)
          = fn_normalize_guest_document_number(NEW.doc_number::text)
      AND fn_guest_document_country(g.doc_country, g.country_id) = v_country_id
    ORDER BY g.id
    LIMIT 1;

IF v_existing_id IS NOT NULL THEN
        RAISE EXCEPTION 'guest document identity already belongs to guest %', v_existing_id
            USING ERRCODE = '23505',
                  HINT = 'Reuse the existing guest or merge duplicate profiles.';
    END IF;

RETURN NEW;
END

sp_tr_guest_identity_guard

Parameters

Definition