Syntax error in Stored Procedure

1

I have a problem compiling, what happens is that it generates syntax error, the detail is that I'm just learning from stored procedures, look at this is my function:

Note: I edited my question and it was already executed correctly, only that it returned to throw another error

CREATE OR REPLACE FUNCTION public.sp_insert_update_trabajo_dave_json(
    param_origin_adm character varying,
    in_json text) 
    RETURNS TABLE(var_mensaje varchar, 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;

    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 vehicle_name=(jsonData->> 'vehicle_name');

    --return 'Json actualizado'
    ELSE
    FOR var_json_fields IN SELECT  * FROM json_array_elements(in_json::json)LOOP    
    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->>'desc_color')::character varying,
               (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')::character varying,
                (var_json->>'fec_param_ini')::character varying,
               (var_json->>'ibase_param_ini')::character varying,
               (var_json->>'odo_param_ini')::character varying,
               (var_json->>'date_last_man')::character varying,
               (var_json->>'cve_delivery_route')::character varying);

           get diagnostics var_n = row_count;
            if(var_n>0)then 
           var_success:=true;
                var_mensaje:='Registro actualizado satisfactoriamente.';
                ELSE
                var_success:=false;
                var_mensaje:='Error en el sistema favor de contactar a su administrador.';
            END IF;
            END LOOP;
        --ELSE
            var_success:=false;
            var_mensaje:='Error en el sistema favor de contactar a su administrador.';
        END IF;
    RETURN QUERY SELECT var_success,var_mensaje;
END;

$BODY$;

The (other) error is:

ERROR: structure of query does not match function result type
SQL state: 42804
Detail: El tipo retornado boolean no coincide con el tipo de registro esperado character varying en la columna 1.
Context: función PL/pgSQL sp_insert_update_trabajo_dave_json(integer,text) en la línea 83 en RETURN QUERY

Note: I have already checked and if you are returning in the return query, but the compiler does not know why you do not accept it.

I hope and you can help me, I will greatly appreciate it, I remain alert to any questions or comments, regards

    
asked by cratus666jose 09.10.2018 в 19:19
source

1 answer

4

The error is that it tells you that you have the UPDATE sentence, you need to give it the value with which you want to update the field vehicle_name , the sentence should be the next way:

UPDATE cat_vehicle SET vehicle_name = 'Valor'
WHERE vehicle_name=(jsonData->> 'vehicle_name');

Where 'Valor' is the value with which you will update the field.

    
answered by 09.10.2018 в 19:29