Last active
January 14, 2019 12:05
-
-
Save asgrdev/54f57891fb51ed909ee912e500aa5052 to your computer and use it in GitHub Desktop.
postegreSql json input function with json rows return
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| CREATE OR REPLACE FUNCTION public.inseupcontacts(data json, img json) | |
| RETURNS SETOF jsonb | |
| LANGUAGE plpgsql | |
| AS $function$ | |
| DECLARE | |
| ref refcursor; | |
| record contacts%ROWTYPE; | |
| I_Max integer := json_array_length(data)-1; | |
| uid integer; | |
| name character varying; | |
| phonen character varying; | |
| numtype character; | |
| email character varying; | |
| image character varying; | |
| jsonrecords jsonb; | |
| BEGIN | |
| FOR I in 0..I_Max LOOP | |
| uid := data->I->>'userid'; | |
| name := data->I->>'name'; | |
| phonen := data->I->>'phonenumber'; | |
| numtype := data->I->>'ctype'; | |
| email := data->I->>'email'; | |
| image := img->>(data->I->>'name')::text; | |
| IF NOT EXISTS (SELECT * FROM users WHERE phonenumber=phonen AND accountstatus=1 ) THEN | |
| OPEN ref FOR WITH updcontact AS (UPDATE contacts SET cname=name, cphonnum=phonen, cemail=email, | |
| cimage=image,userisexite=true, cuserid=0, | |
| time=Now() WHERE userid=uid AND cphonnum=phonen AND userisexite=false RETURNING *) | |
| INSERT INTO contacts (userid,cname,cphonnum,cpnumtype,cemail,cimage,userisexite,cuserid,time) | |
| SELECT uid,name ,phonen ,numtype ,email,image,false,0, NOW() WHERE NOT EXISTS (SELECT * FROM updcontact) | |
| AND NOT EXISTS (SELECT id FROM contacts WHERE userid=uid AND cname=name AND | |
| cphonnum=phonen AND cemail=email AND cimage=image) RETURNING * ; | |
| ELSE | |
| OPEN ref FOR WITH updcontact AS (UPDATE contacts SET cname=name, cphonnum=phonen, cemail=email, | |
| cimage=image,userisexite=true, cuserid=(SELECT id FROM users WHERE phonenumber=phonen AND accountstatus=1), | |
| time=Now() WHERE userid=uid AND cphonnum=phonen AND userisexite=false RETURNING *) | |
| INSERT INTO contacts (userid,cname,cphonnum,cpnumtype,cemail,cimage,userisexite,cuserid,time) | |
| SELECT uid,name ,phonen ,numtype ,email,image,true, | |
| (SELECT id FROM users WHERE phonenumber=phonen AND accountstatus=1), NOW() WHERE NOT EXISTS (SELECT * FROM updcontact) | |
| AND NOT EXISTS (SELECT id FROM contacts WHERE userid=uid AND cname=name AND cphonnum=phonen AND cemail=email AND cimage=image) RETURNING * ; | |
| END IF; | |
| FETCH ref INTO record; | |
| RETURN NEXT row_to_json(record)::jsonb; | |
| CLOSE ref; | |
| END LOOP; | |
| RETURN; | |
| END; | |
| $function$ |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment