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