Estimados Compañeros estoy trabajando con Postgres v12.x, he escrito un procedimiento Almacenado que me devuelva el resultado en JSON
Y me entrega el siguiente error, favor me podran orientar de como hacerlos??... Muchas Gracias..
Este es el procedimiento:
Dear Colleagues I am working with Postgres v12.x, I have written a Stored procedure that returns the result in JSON
And it gives me the following error, could you please guide me on how to do them? ... Thank you very much ..
This is the procedure:
And it gives me the following error, could you please guide me on how to do them? ... Thank you very much ..
This is the procedure:
CREATE OR REPLACE FUNCTION public.sp_ccmm_json_9( param integer, periodo integer )
RETURNS json
LANGUAGE plpgsql
AS $function$
begin
declare ret JSON;
SELECT to_json(salida.*) AS to_json
FROM ( SELECT to_char(t2.fecha_analista::timestamp with time zone, 'Month'::text) AS mes,
count(t2.fecha_analista) AS a,
count(t2.fecha_exp) AS r
FROM tb_analistas t1
JOIN tb_ingreso_siabc t2 ON t1.id_an = t2.analista
WHERE t1.id_an = param::double precision AND date_part('year'::text, t2.fecha_analista) = periodo::double precision AND t2.tipo_ccmm = 1
GROUP BY (to_char(t2.fecha_analista::timestamp with time zone, 'Month'::text))
UNION
SELECT to_char(t2.fecha_analista::timestamp with time zone, 'Month'::text) AS mes,
count(t2.fecha_analista) AS a,
count(t2.fecha_exp) AS r
FROM tb_analistas t1
JOIN tb_ingreso_siabc t2 ON t1.id_an = t2.analista
WHERE t1.id_an = param::double precision AND date_part('year'::text, t2.fecha_analista) = periodo::double precision AND t2.tipo_ccmm = 2
GROUP BY (to_char(t2.fecha_analista::timestamp with time zone, 'Month'::text))
UNION
SELECT to_char(t2.fecha_analista::timestamp with time zone, 'Month'::text) AS mes,
count(t2.fecha_analista) AS a,
count(t2.fecha_exp) AS r
FROM tb_analistas t1
JOIN tb_ingreso_siabc t2 ON t1.id_an = t2.analista
WHERE t1.id_an = param::double precision AND date_part('year'::text, t2.fecha_analista) = periodo::double precision AND t2.tipo_ccmm = 3
GROUP BY (to_char(t2.fecha_analista::timestamp with time zone, 'Month'::text))) salida
ORDER BY salida.mes, salida.a, salida.r;
END;
$function$
RETURNS json
LANGUAGE plpgsql
AS $function$
begin
declare ret JSON;
SELECT to_json(salida.*) AS to_json
FROM ( SELECT to_char(t2.fecha_analista::timestamp with time zone, 'Month'::text) AS mes,
count(t2.fecha_analista) AS a,
count(t2.fecha_exp) AS r
FROM tb_analistas t1
JOIN tb_ingreso_siabc t2 ON t1.id_an = t2.analista
WHERE t1.id_an = param::double precision AND date_part('year'::text, t2.fecha_analista) = periodo::double precision AND t2.tipo_ccmm = 1
GROUP BY (to_char(t2.fecha_analista::timestamp with time zone, 'Month'::text))
UNION
SELECT to_char(t2.fecha_analista::timestamp with time zone, 'Month'::text) AS mes,
count(t2.fecha_analista) AS a,
count(t2.fecha_exp) AS r
FROM tb_analistas t1
JOIN tb_ingreso_siabc t2 ON t1.id_an = t2.analista
WHERE t1.id_an = param::double precision AND date_part('year'::text, t2.fecha_analista) = periodo::double precision AND t2.tipo_ccmm = 2
GROUP BY (to_char(t2.fecha_analista::timestamp with time zone, 'Month'::text))
UNION
SELECT to_char(t2.fecha_analista::timestamp with time zone, 'Month'::text) AS mes,
count(t2.fecha_analista) AS a,
count(t2.fecha_exp) AS r
FROM tb_analistas t1
JOIN tb_ingreso_siabc t2 ON t1.id_an = t2.analista
WHERE t1.id_an = param::double precision AND date_part('year'::text, t2.fecha_analista) = periodo::double precision AND t2.tipo_ccmm = 3
GROUP BY (to_char(t2.fecha_analista::timestamp with time zone, 'Month'::text))) salida
ORDER BY salida.mes, salida.a, salida.r;
END;
$function$
Este Error en NodeJS cuando intenta recuperar el set de registros...
This Error in NodeJS when trying to retrieve the recordset ...
More Thanks...
Atentamente,
Carlos Armijo Severino
Ingeniero de Ejecución en Informática
Celular : +56 9776 19 419
|
|
Remitente notificado con Mailtrack 22/02/21 15:58:12 |
