How to use the LIKE as a parameter in a function in postgres

0

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.

    
asked by jeissoni22 10.04.2017 в 22:31
source

1 answer

1

The simple quote (') that you open in the Like is closing the one you opened when you started the declaration of your function, so the percentage symbol is not being taken as a string, done if you notice even the color is changed

try putting

LIKE regional||''%''

that is, twice each single quote

    
answered by 10.04.2017 в 23:56