Hello everyone, I am trying to pass a query that I use regularly to a function but I am having problems creating 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 gives me an error when trying to create the function, it tells me that I have an error in regional||'%' I appreciate any help.
The single quote (') that you open in the Like is closing the one that you opened when you started your function declaration, so the percentage symbol is not being taken as a string, in fact if you look even the color is changed
try putting
i.e. twice each single quote