Problems with my Store Procedure, do not insert values with json

1

I have a problem with an SP, what happens is that when you run it, it receives a json as a parameter, and when it is executed, it returns a false message as an error message, this is my code, I am not Very much in this since I am learning:

CREATE OR REPLACE FUNCTION public.sp_insert_update_trabajo_dave_json(
    in_json text)
    RETURNS TABLE(var_mensaje character varying, var_success boolean) 
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
    ROWS 1000
AS $BODY$

DECLARE
jsonData json;
var_count integer;
var_json json;
var_json_fields json;
var_n integer;
var_mensaje varchar;
var_success boolean:=false;
var_counts integer;
BEGIN
    jsonData := in_json:: json;
    FOR var_json_fields IN SELECT * FROM json_array_elements(in_json::json) LOOP    
    --SELECT COUNT (*) FROM cat_vehicle 
    --where cve_vehicle =(jsonData->>'cve_vehicle'):: integer into var_counts;
       --IF (var_counts >0)THEN
            --UPDATE cat_vehicle SET vehicle_name =(jsonData->>'vehicle_name')
            --WHERE cve_vehicle=(jsonData->>'cve_vehicle'):: integer;
    --END IF;
    --END LOOP;

    --ELSE
    IF(var_json_fields ISNULL)THEN
    INSERT INTO cat_vehicle(
        origin_adm,
        cve_vehicle,
        vehicle_name, 
        cve_brand, 
        vehicle_plate, 
        vehicle_vin, 
        cve_model, 
        vehicle_year, 
        cve_color, 
        vehicle_capacity, 
        cve_type, 
        cve_vehicle_cat,
        vehicle_image,
        status, 
        user_cve, 
        last_update,
        fec_param_ini, 
        lbase_param_ini, 
        odo_param_ini, 
        date_last_man, 
        odo_last_man,
        cve_delivery_route) VALUES(
          (var_json->>'origin_adm') :: integer,
               (var_json->>'cve_vehicle'):: integer,
               (var_json->>'vehicle_name'):: character varying,
               (var_json->>'cve_brand'):: integer,
               (var_json->>'vehicle_plate'):: character varying,
               (var_json->>'vehicle_vin'):: character varying,
               (var_json->>'cve_model'):: integer,
               (var_json->>'vehicle_year'):: character varying,
               (var_json->>'cve_color'):: integer,
               (var_json->>'vehicle_capacity'):: integer,
               (var_json->>'cve_type'):: integer,
               (var_json->>'cve_vehicle_cat'):: integer,
               (var_json->>'vehicle_image'):: character varying,
               (var_json->>'status'):: character varying,
                (var_json->>'user_cve'):: character varying,
               (var_json->>'last_update'):: date,
                (var_json->>'fec_param_ini'):: date,
               (var_json->>'lbase_param_ini'):: integer,
               (var_json->>'odo_param_ini'):: integer,
               (var_json->>'date_last_man'):: date,
                (var_json->>'odo_last_man'):: integer,
               (var_json->>'cve_delivery_route'):: integer);     
             END IF;
           END LOOP;
     get diagnostics var_n = row_count;
            IF(var_n > 0) THEN
                var_success:=true;
                var_mensaje:='Registros agregados satisfactoriamente.';
            ELSE
                var_success:=false;
                var_mensaje:='Error en el sistema favor de contactar a su administrador.';
                --var_id:=0;
            END IF;
    --end if;
    RETURN QUERY SELECT var_mensaje, var_success;
END;

$BODY$;

This is the json I want to insert:

[
      {
    "origin_adm": 1,
    "company_name": "PHOENIX",
    "cve_vehicle": 854,
    "vehicle_name": "ECO-KJU",
    "cve_brand": 2,
    "desc_brand": "Nissan",
    "Vehicle_plate": "1",
    "vehicle_vin": "1",
    "cve_model": 2,
    "desc_model": "TSURU",
    "vehicle_year": "2010",
    "cve_color": 2,
    "vehicle_capacity": 0,
    "cve_type": 4,
    "vehicle_image": "",
    "status": "1",
    "user_cve":"usrcve",
    "last_update":"2018-04-14",
    "fec_param_ini":"2018-04-11",
    "ibase_param_ini":"2018-02-11",
    "odo_param_ini":"null",
    "date_last_man":"2018-02-15"
  },
      {
    "origin_adm": 1,
    "company_name": "PHOENIX",
    "cve_vehicle": 854,
    "vehicle_name": "ECOTR",
    "cve_brand": 2,
    "desc_brand": "Nissan",
    "Vehicle_plate": "1",
    "vehicle_vin": "1",
    "cve_model": 2,
    "desc_model": "TSURU",
    "vehicle_year": "2010",
    "cve_color": 2,
    "vehicle_capacity": 0,
    "cve_type": 4,
    "vehicle_image": "",
    "status": "1",
    "user_cve":"usrcve",
    "last_update":"2018-04-14",
    "fec_param_ini":"2018-04-11",
    "ibase_param_ini":"2018-02-11",
    "odo_param_ini":"null",
    "date_last_man":"2018-02-15"
  },
      {
    "origin_adm": 1,
    "company_name": "PHOENIX",
    "cve_vehicle": 854,
    "vehicle_name": "ECO-YUY",
    "cve_brand": 2,
    "desc_brand": "Nissan",
    "Vehicle_plate": "1",
    "vehicle_vin": "1",
    "cve_model": 2,
    "desc_model": "TSURU",
    "vehicle_year": "2010",
    "cve_color": 2,
    "vehicle_capacity": 0,
    "cve_type": 4,
    "vehicle_image": "",
    "status": "1",
    "user_cve":"usrcve",
    "last_update":"2018-04-14",
    "fec_param_ini":"2018-04-11",
    "ibase_param_ini":"2018-02-11",
    "odo_param_ini":"null",
    "date_last_man":"2018-02-15"
  },
      {
    "origin_adm": 1,
    "company_name": "PHOENIX",
    "cve_vehicle": 854,
    "vehicle_name": "777775fff",
    "cve_brand": 2,
    "desc_brand": "Nissan",
    "Vehicle_plate": "1",
    "vehicle_vin": "1",
    "cve_model": 2,
    "desc_model": "TSURU",
    "vehicle_year": "2010",
    "cve_color": 2,
    "vehicle_capacity": 0,
    "cve_type": 4,
    "vehicle_image": "",
    "status": "1",
    "user_cve":"usrcve",
    "last_update":"2018-04-14",
    "fec_param_ini":"2018-04-11",
    "ibase_param_ini":"2018-02-11",
    "odo_param_ini":"null",
    "date_last_man":"2018-02-15"
  },
      {
    "origin_adm": 1,
    "company_name": "PHOENIX",
    "cve_vehicle": 854,
    "vehicle_name": "777775fff",
    "cve_brand": 2,
    "desc_brand": "Nissan",
    "Vehicle_plate": "1",
    "vehicle_vin": "1",
    "cve_model": 2,
    "desc_model": "TSURU",
    "vehicle_year": "2010",
    "cve_color": 2,
    "vehicle_capacity": 0,
    "cve_type": 4,
    "vehicle_image": "",
    "status": "1",
    "user_cve":"usrcve",
    "last_update":"2018-04-14",
    "fec_param_ini":"2018-04-11",
    "ibase_param_ini":"2018-02-11",
    "odo_param_ini":"null",
    "date_last_man":"2018-02-15"
  },
      {
    "origin_adm": 1,
    "company_name": "PHOENIX",
    "cve_vehicle": 854,
    "vehicle_name": "777775fff",
    "cve_brand": 2,
    "desc_brand": "Nissan",
    "Vehicle_plate": "1",
    "vehicle_vin": "1",
    "cve_model": 2,
    "desc_model": "TSURU",
    "vehicle_year": "2010",
    "cve_color": 2,
    "vehicle_capacity": 0,
    "cve_type": 4,
    "vehicle_image": "",
    "status": "1",
    "user_cve":"usrcve",
    "last_update":"2018-04-14",
    "fec_param_ini":"2018-04-11",
    "ibase_param_ini":"2018-02-11",
    "odo_param_ini":"null",
    "date_last_man":"2018-02-15"
  },
     {
    "origin_adm": 1,
    "company_name": "PHOENIX",
    "cve_vehicle": 854,
    "vehicle_name": "777775fff",
    "cve_brand": 2,
    "desc_brand": "Nissan",
    "Vehicle_plate": "1",
    "vehicle_vin": "1",
    "cve_model": 2,
    "desc_model": "TSURU",
    "vehicle_year": "2010",
    "cve_color": 2,
    "vehicle_capacity": 0,
    "cve_type": 4,
    "vehicle_image": "",
    "status": "1",
    "user_cve":"usrcve",
    "last_update":"2018-04-14",
    "fec_param_ini":"2018-04-11",
    "ibase_param_ini":"2018-02-11",
    "odo_param_ini":"null",
    "date_last_man":"2018-02-15"
  },
      {
    "origin_adm": 1,
    "company_name": "PHOENIX",
    "cve_vehicle": 854,
    "vehicle_name": "777775fff",
    "cve_brand": 2,
    "desc_brand": "Nissan",
    "Vehicle_plate": "1",
    "vehicle_vin": "1",
    "cve_model": 2,
    "desc_model": "TSURU",
    "vehicle_year": "2010",
    "cve_color": 2,
    "vehicle_capacity": 0,
    "cve_type": 4,
    "vehicle_image": "",
    "status": "1",
    "user_cve":"usrcve",
    "last_update":"2018-04-14",
    "fec_param_ini":"2018-04-11",
    "ibase_param_ini":"2018-02-11",
    "odo_param_ini":"null",
    "date_last_man":"2018-02-15"
  },
      {
    "origin_adm": 1,
    "company_name": "PHOENIX",
    "cve_vehicle": 854,
    "vehicle_name": "777775fff",
    "cve_brand": 2,
    "desc_brand": "Nissan",
    "Vehicle_plate": "1",
    "vehicle_vin": "1",
    "cve_model": 2,
    "desc_model": "TSURU",
    "vehicle_year": "2010",
    "cve_color": 2,
    "vehicle_capacity": 0,
    "cve_type": 4,
    "vehicle_image": "",
    "status": "1",
    "user_cve":"usrcve",
    "last_update":"2018-04-14",
    "fec_param_ini":"2018-04-11",
    "ibase_param_ini":"2018-02-11",
    "odo_param_ini":"null",
    "date_last_man":"2018-02-15"
  },
      {
    "origin_adm": 1,
    "company_name": "PHOENIX",
    "cve_vehicle": 854,
    "vehicle_name": "777775fff",
    "cve_brand": 2,
    "desc_brand": "Nissan",
    "Vehicle_plate": "1",
    "vehicle_vin": "1",
    "cve_model": 2,
    "desc_model": "TSURU",
    "vehicle_year": "2010",
    "cve_color": 2,
    "vehicle_capacity": 0,
    "cve_type": 4,
    "vehicle_image": "",
    "status": "1",
    "user_cve":"usrcve",
    "last_update":"2018-04-14",
    "fec_param_ini":"2018-04-11",
    "ibase_param_ini":"2018-02-11",
    "odo_param_ini":"null",
    "date_last_man":"2018-02-15"
  },
      {
    "origin_adm": 1,
    "company_name": "PHOENIX",
    "cve_vehicle": 854,
    "vehicle_name": "777775fff",
    "cve_brand": 2,
    "desc_brand": "Nissan",
    "Vehicle_plate": "1",
    "vehicle_vin": "1",
    "cve_model": 2,
    "desc_model": "TSURU",
    "vehicle_year": "2010",
    "cve_color": 2,

    "vehicle_capacity": 0,
    "cve_type": 4,
    "vehicle_image": "",
    "status": "1",
    "user_cve":"usrcve",
    "last_update":"2018-04-14",
    "fec_param_ini":"2018-04-11",
    "ibase_param_ini":"2018-02-11",
    "odo_param_ini":"null",
    "date_last_man":"2018-02-15"
  },
     {
    "origin_adm": 1,
    "company_name": "PHOENIX",
    "cve_vehicle": 854,
    "vehicle_name": "777775fff",
    "cve_brand": 2,
    "desc_brand": "Nissan",
    "Vehicle_plate": "1",
    "vehicle_vin": "1",
    "cve_model": 2,
    "desc_model": "TSURU",
    "vehicle_year": "2010",
    "cve_color": 2,
    "vehicle_capacity": 0,
    "cve_type": 4,
    "vehicle_image": "",
    "status": "1",
    "user_cve":"usrcve",
    "last_update":"2018-04-14",
    "fec_param_ini":"2018-04-11",
    "ibase_param_ini":"2018-02-11",
    "odo_param_ini":"null",
    "date_last_man":"2018-02-15"
  },
      {
    "origin_adm": 1,
    "company_name": "PHOENIX",
    "cve_vehicle": 854,
    "vehicle_name": "777775fff",
    "cve_brand": 2,
    "desc_brand": "Nissan",
    "Vehicle_plate": "1",
    "vehicle_vin": "1",
    "cve_model": 2,
    "desc_model": "TSURU",
    "vehicle_year": "2010",
    "cve_color": 2,
    "vehicle_capacity": 0,
    "cve_type": 4,
    "vehicle_image": "",
    "status": "1",
    "user_cve":"usrcve",
    "last_update":"2018-04-14",
    "fec_param_ini":"2018-04-11",
    "ibase_param_ini":"2018-02-11",
    "odo_param_ini":"null",
    "date_last_man":"2018-02-15"
  },
      {
    "origin_adm": 1,
    "company_name": "PHOENIX",
    "cve_vehicle": 854,
    "vehicle_name": "777775fff",
    "cve_brand": 2,
    "desc_brand": "Nissan",
    "Vehicle_plate": "1",
    "vehicle_vin": "1",
    "cve_model": 2,
    "desc_model": "TSURU",
    "vehicle_year": "2010",
    "cve_color": 2,
    "vehicle_capacity": 0,
    "cve_type": 4,
    "vehicle_image": "",
    "status": "1",
    "user_cve":"usrcve",
    "last_update":"2018-04-14",
    "fec_param_ini":"2018-04-11",
    "ibase_param_ini":"2018-02-11",
    "odo_param_ini":"null",
    "date_last_man":"2018-02-15"
  },
      {
    "origin_adm": 1,
    "company_name": "PHOENIX",
    "cve_vehicle": 854,
    "vehicle_name": "777775fff",
    "cve_brand": 2,
    "desc_brand": "Nissan",
    "Vehicle_plate": "1",
    "vehicle_vin": "1",
    "cve_model": 2,
    "desc_model": "TSURU",
    "vehicle_year": "2010",
    "cve_color": 2,
    "vehicle_capacity": 0,
    "cve_type": 4,
    "vehicle_image": "",
    "status": "1",
    "user_cve":"usrcve",
    "last_update":"2018-04-14",
    "fec_param_ini":"2018-04-11",
    "ibase_param_ini":"2018-02-11",
    "odo_param_ini":"null",
    "date_last_man":"2018-02-15"
  },
      {
    "origin_adm": 1,
    "company_name": "PHOENIX",
    "cve_vehicle": 854,
    "vehicle_name": "777775fff",
    "cve_brand": 2,
    "desc_brand": "Nissan",
    "Vehicle_plate": "1",
    "vehicle_vin": "1",
    "cve_model": 2,
    "desc_model": "TSURU",
    "vehicle_year": "2010",
    "cve_color": 2,
    "vehicle_capacity": 0,
    "cve_type": 4,
    "vehicle_image": "",
    "status": "1",
    "user_cve":"usrcve",
    "last_update":"2018-04-14",
    "fec_param_ini":"2018-04-11",
    "ibase_param_ini":"2018-02-11",
    "odo_param_ini":"null",
    "date_last_man":"2018-02-15"
  },
      {
    "origin_adm": 1,
    "company_name": "PHOENIX",
    "cve_vehicle": 854,
    "vehicle_name": "777775fff",
    "cve_brand": 2,
    "desc_brand": "Nissan",
    "Vehicle_plate": "1",
    "vehicle_vin": "1",
    "cve_model": 2,
    "desc_model": "TSURU",
    "vehicle_year": "2010",
    "cve_color": 2,
    "vehicle_capacity": 0,
    "cve_type": 4,
    "vehicle_image": "",
    "status": "1",
    "user_cve":"usrcve",
    "last_update":"2018-04-14",
    "fec_param_ini":"2018-04-11",
    "ibase_param_ini":"2018-02-11",
    "odo_param_ini":"null",
    "date_last_man":"2018-02-15"
  },
      {
    "origin_adm": 1,
    "company_name": "PHOENIX",
    "cve_vehicle": 854,
    "vehicle_name": "777775fff",
    "cve_brand": 2,
    "desc_brand": "Nissan",
    "Vehicle_plate": "1",
    "vehicle_vin": "1",
    "cve_model": 2,
    "desc_model": "TSURU",
    "vehicle_year": "2010",
    "cve_color": 2,
    "vehicle_capacity": 0,
    "cve_type": 4,
    "vehicle_image": "",
    "status": "1",
    "user_cve":"usrcve",
    "last_update":"2018-04-14",
    "fec_param_ini":"2018-04-11",
    "ibase_param_ini":"2018-02-11",
    "odo_param_ini":"null",
    "date_last_man":"2018-02-15"
  },
      {
    "origin_adm": 1,
    "company_name": "PHOENIX",
    "cve_vehicle": 854,
    "vehicle_name": "777775fff",
    "cve_brand": 2,
    "desc_brand": "Nissan",
    "Vehicle_plate": "1",
    "vehicle_vin": "1",
    "cve_model": 2,
    "desc_model": "TSURU",
    "vehicle_year": "2010",
    "cve_color": 2,
    "vehicle_capacity": 0,
    "cve_type": 4,
    "vehicle_image": "",
    "status": "1",
    "user_cve":"usrcve",
    "last_update":"2018-04-14",
    "fec_param_ini":"2018-04-11",
    "ibase_param_ini":"2018-02-11",
    "odo_param_ini":"null",
    "date_last_man":"2018-02-15"
  }
    ]

I hope and you can help me in telling me please that I am watering it, as I see it if it is "OK" I keep an eye on your comments, greetings.

    
asked by cratus666jose 10.10.2018 в 23:43
source

1 answer

0

The code was fine, just needed to correct the invalidation validation of the JSON

FROM: IF (var_json_fields ISNULL) THEN

A: IF (var_json_fields IS NOT NULL ) THEN

You can find a test code for that answer in:

link

There are also two branches:   q203626-s1 With the initial state of test with the error   q203626-s2 With the status corrected.

I hope it serves you.

    
answered by 12.10.2018 в 06:48