aonestar
.public
Tables
(current)
Columns
Constraints
Relationships
Orphan Tables
Anomalies
Routines
sp_delete_guest_document_json
Parameters
Name
Type
Mode
p_data
jsonb
IN
Definition
declare result_json json; eff_result int := 0; v_mode text; v_tag text; v_guest_doc_id int; v_guest_doc_record record; v_data_item jsonb; begin v_mode := p_data ->> 'mode'; if v_mode = 's' then v_guest_doc_id := (p_data -> 'data' ->> 'guest_doc_id')::int; with deleted as ( delete from guest_document where id = v_guest_doc_id returning * ) select count(*) into eff_result from deleted; elsif v_mode = 'g' then for v_data_item in select * from jsonb_array_elements(p_data -> 'data') loop v_tag := v_data_item ->> 'tag'; v_guest_doc_id := (v_data_item ->> 'guest_doc_id')::int; with deleted as ( delete from guest_document where id = v_guest_doc_id and tag = v_tag and guest_id is null returning * ) select count(*) into eff_result from deleted; end loop; end if; select json_agg( json_build_object( 'guest_document_id', gd.id, 'photo_url', coalesce(gd.photo_url, ''), 'name', coalesce((select g.first_name || ' ' || g.last_name from guest g where g.id=gd.guest_id limit 1),''), 'sex', coalesce((select g.sex from guest g where g.id = gd.guest_id limit 1), ''), 'passport', coalesce(gd.doc_number,''), 'country', coalesce((select c.name from country c where c.id = (select g.country_id from guest g where g.id=gd.guest_id limit 1) limit 1), ''), 'nationality',coalesce((select n.name from nationality n where n.id = gd.nation_id limit 1), ''), 'birth_date', coalesce((select birthdate from guest g where g.id = gd.guest_id limit 1)::text, '') ) order by id asc ) into result_json from guest_document gd; return coalesce(result_json, '[]'::json); end;