Hello everyone, I am trying to pass a query that I use regularly to a function but I run into problems to create it. What I have so far is the following
CREATE OR REPLACE FUNCTION "public"."prueba" (in regional varchar, in fecha date) RETURNS TABLE(num_recepcion varchar, nombre varchar, nit varchar, nombre_ips varchar, total int4, tipificacada int4, devuelta int4, fecha_recepcion date, fecha_asignacion date, nombre_operador varchar, tipo int4) AS
'
BEGIN
RETURN QUERY
SELECT
TIPI."NUMERO_RECEPCION",
TIPI."NIT_IPS",
TIPI."NOMBRE_IPS",
TIPI.Total,
TIPI.Tipificadas,
DEVUELTA.devultas,
TIPI."FECHA_RECEPCION" AS Fecha_Entrada,
CF."FechaAsignacion" AS Fecha_Asignado,
TIPI."NOMBRE_OPERADOR_ASIGNADO",
TIPI."ESTADO"
FROM
( SELECT
Y."NUMERO_RECEPCION",
RF."NIT_IPS",
RF."NOMBRE_IPS",
Y.Total,
Y.Tipificadas,
RF."FECHA_RECEPCION",
RF."NOMBRE_OPERADOR_ASIGNADO",
RF."ESTADO"
FROM
"t_RecepcionFacturacion" AS RF,
( SELECT
X."NUMERO_RECEPCION",
SUM(X.total) AS Total,
SUM(X.TIPIFICADAS) AS Tipificadas
FROM
( SELECT
SUB."NUMERO_RECEPCION",
COUNT(*) total ,
COUNT (SUB."ESTADO") AS TIPIFICADAS
FROM
( SELECT
DISTINCT DRF."NUMERO_RECEPCION",
DRF."NUMERO_FACTURA",
DRF."ESTADO",
DRF."ESTADO_RECEPCION"
FROM
"t_DetalleRecepcionFacturacion" AS DRF
JOIN "t_RecepcionFacturacion" AS RF
ON DRF."NUMERO_RECEPCION"= RF."NUMERO_RECEPCION"
WHERE
RF."FECHA_RECEPCION" = fecha AND
RF."NUMERO_RECEPCION" LIKE regional||'%') AS
SUB
GROUP BY
SUB."NUMERO_RECEPCION",
SUB."ESTADO") AS X
GROUP BY
X."NUMERO_RECEPCION") Y
WHERE
RF."NUMERO_RECEPCION" = Y."NUMERO_RECEPCION") AS TIPI
LEFT JOIN ( SELECT
rf."NUMERO_RECEPCION",
RF."NIT_IPS",
RF."NOMBRE_IPS",
X.devultas
FROM
"t_RecepcionFacturacion" AS rf,
( SELECT
SUB."NUMERO_RECEPCION",
COUNT(*) AS devultas
FROM
( SELECT
DISTINCT DRF."NUMERO_RECEPCION",
DRF."NUMERO_FACTURA"
FROM
"t_DetalleRecepcionFacturacion" AS drf
WHERE
drf."NUMERO_RECEPCION" LIKE regional||'%'
AND
drf."ESTADO_RECEPCION"='8') AS SUB
GROUP BY
SUB."NUMERO_RECEPCION")
AS X
WHERE
rf."NUMERO_RECEPCION" = X."NUMERO_RECEPCION") AS
DEVUELTA
ON TIPI."NUMERO_RECEPCION"= DEVUELTA."NUMERO_RECEPCION"
JOIN "t_ConsecutivosRecibidos" AS CF ON TIPI."NUMERO_RECEPCION" = CF."NumeroConsecutivo";
END
'
LANGUAGE 'plpgsql'
But it generates an error when trying to create the function, it tells me that I have an error in regional || '%' I appreciate any help.