aonestar.public

Parameters

Name	Type	Mode
p_data	jsonb	IN
i_photo	bytea	IN (DEFAULT NULL)

Definition

declare
    v_result json;
    v_msg_text text;
    v_sqlstate text;
    v_detail text;
    v_hint text;
    v_context text;

    v_register_id int4;
    v_doc_id int;
    v_doc_data jsonb;
    v_new_guest_id int4;
    v_new_guests int[] = '{}';
begin
    /*
    {
        "register_id" : int,
        "mode" : ,
        "arrival_date": 2025-06-20
        "moreguest" : [
            {
                "guest_doc_id" : int,
                "tag": ""
            },
            {
                "guest_doc_id" : int,
                "tag": ""
            }
        ]
    }
    */

    perform sp_log_debug('sp_more_guest_assign_passport_json', p_data::text, True);

    v_register_id  := (p_data ->> 'register_id')::int4;


    for v_doc_id, v_doc_data in
       select d.id, d.doc_data
       from jsonb_to_recordset(p_data -> 'moreguest') as m(guest_doc_id int)
            inner join guest_document d on m.guest_doc_id = d.id
    loop
        v_new_guest_id := sp_save_guest_document(v_doc_data);
        v_new_guests := v_new_guests || v_new_guest_id;

        insert into register_guests (register_id, guest_id)
        values (v_register_id, v_new_guest_id)
        on conflict (register_id, guest_id) do nothing;

        update guest_document
        set guest_id = v_new_guest_id--, tag = null
        where id = v_doc_id;

        update guest g
        set doc_id = v_doc_id
        where g.id = v_new_guest_id;
    end loop;

    return fn_result_success(json_build_object('guest_id', v_new_guests));

exception
    when others then
        get stacked diagnostics
            v_msg_text = message_text,
            v_sqlstate = returned_sqlstate,
            v_detail = pg_exception_detail,
            v_hint = pg_exception_hint,
            v_context = pg_exception_context;

        return fn_handle_error(
            v_sqlstate,
            v_msg_text,
            v_detail,
            v_hint,
            v_context,
            'sp_more_guest_assign_passport_json',
            p_data
        );
end;