POSTGRE SQL

Transaction and Exception handling

do $$  

declare

 id numeric := 0;

begin

Query1 ;

  Query2;

   id:=1;

 

exception

          when others then 

               raise notice 'ERROR % %' ,sqlstate, id;

      rollBACK;

COMMIT;

end; $$ 

language 'plpgsql';

Use JSON Data as parameter  and get using table

"Role" table has two columns ---

 -- FUNCTION: public."Get_JSON_TO_TABLE"(json)

--select * from "Get_JSON_TO_TABLE"('{"RoleName":"Test","Description":"test desc"}')

-- DROP FUNCTION public."Get_JSON_TO_TABLE"(json);

/*

select * from "Get_JSON_TO_TABLE"('{"RoleName":"Test","Description":"test desc"}')

*/

CREATE OR REPLACE FUNCTION public."Get_JSON_TO_TABLE"(

jsondata json)

    RETURNS Table("RoleNam" character varying,"Descripti" character varying)

    LANGUAGE 'plpgsql'    

AS $BODY$        

    

BEGIN

RETURN QUERY

select "RoleName","Description" from json_populate_record(null::"Role", jsondata) As "test";

END;

$BODY$;

ALTER FUNCTION public."Get_JSON_TO_TABLE"(json)

    OWNER TO postgres;