Error en los parámetros plpgsql en la cláusula WHERE

20 Jul, 2023 Programación 0

Creé una función utilizada para filtrar.

CREATE OR REPLACE FUNCTION filtersearch(
    val1 text[] DEFAULT NULL::text[],
    val2 text[] DEFAULT NULL::text[],
    val3 text[] DEFAULT NULL::text[],
    val4 boolean DEFAULT NULL::boolean)
    RETURNS SETOF student_table
    LANGUAGE 'plpgsql'

AS $BODY$

DECLARE
   where_val text := '';
   result_text int;
BEGIN
    IF $4 = true THEN
        where_val := 'attendance = true';
        raise notice 'Value: %', where_val;
    END IF;

    RETURN QUERY EXECUTE    
    'SELECT * FROM student_table
    WHERE (LOWER("student_name") = ($1) OR $1 IS NULL)
    AND ((LOWER("subject") = ($2)) OR $2 IS NULL)
    AND ((LOWER("comments") ILIKE $3) 
        OR ($3 IS NULL) 
        )
    '|| where_val ||'' USING where_val;
    --raise notice 'Value: %', result_text;
END 
$BODY$;

Tengo un error al llamarlo,

ERROR: op ANY/ALL (array) requires array on right side

Cualquier sugerencia o comentario en mi código es muy apreciado.

Aquí está el ejemplo de los datos de mi tabla:

| student_name | subject | professor    | comments | attendance |
|--------------|---------|--------------|----------|------------|
| Amelia       | Math    | Digory Kirkle|          | TRUE       |
| Benjamin     | Science | Hari Seldon  |          | FALSE      |
| Charlotte    | English | Lee Everett  |          | TRUE       |
| Amelia       | English | Lee Everett  |          | FALSE      |

Estoy llamando el código de esta manera:

SELECT filtersearch(
    (array['Amelia', 'Charlotte']::text[]), 
    (NULL), 
    (NULL), 
    (true)
)

davy.ai

July 20, 2023 at 6:20 am

El error “op ANY/ALL (array) requires array on right side” sugiere que puede haber un problema en la forma en que se utilizan los arrays en la cláusula WHERE. Específicamente, parece que se está utilizando un array en el lado izquierdo de un operador de comparación que espera un array en el lado derecho.

Tras inspeccionar el código, parece que el problema puede estar con el uso de la función “LOWER” en las columnas “student_name” y “subject”. Dado que la entrada es un array de cadenas de texto, se está aplicando la función “LOWER” a todo el array, lo cual genera un error cuando la cláusula WHERE intenta comparar el array en el lado izquierdo con una sola cadena en el lado derecho.

Para solucionar esto, la función “LOWER” debería aplicarse a cada elemento del array utilizando la función “UNNEST”. Aquí tienes una versión actualizada del código que debería funcionar:

CREATE OR REPLACE FUNCTION filtersearch(
    val1 text[] DEFAULT NULL::text[],
    val2 text[] DEFAULT NULL::text[],
    val3 text[] DEFAULT NULL::text[],
    val4 boolean DEFAULT NULL::boolean)
RETURNS SETOF student_table
LANGUAGE 'plpgsql'

AS $BODY$

DECLARE
   where_val text := '';
BEGIN

    IF $4 = true THEN
        where_val := ' AND attendance = true';
    END IF;

RETURN QUERY EXECUTE    
'SELECT * FROM student_table
    WHERE (
        SELECT UNNEST(LOWER(val1))
    ) = LOWER(student_name) OR val1 IS NULL
    AND (
        SELECT UNNEST(LOWER(val2))
    ) = LOWER(subject) OR val2 IS NULL
    AND (
        LOWER(comments) LIKE ANY(val3) OR val3 IS NULL
    )
    ' || where_val || '';
END 
$BODY$;

En esta versión actualizada, se utiliza la función “UNNEST” para recorrer cada elemento de los arrays de entrada, y la función “LOWER” se aplica a cada elemento individualmente. La cláusula WHERE también utiliza el operador “LIKE ANY” para comparar la columna “comments” con cada elemento del array “val3”.

Ahora, al llamar a la función con los datos de muestra dados, la siguiente consulta debería funcionar:

SELECT filtersearch(
    (array['Amelia', 'Charlotte']::text[]), 
    (NULL), 
    (NULL), 
    (true)
)

Answer

davy.ai

July 20, 2023 at 6:20 am
El error “op ANY/ALL (array) requires array on right side” sugiere que puede haber un problema en la forma en que se utilizan los arrays en la cláusula WHERE. Específicamente, parece que se está utilizando un array en el lado izquierdo de un operador de comparación que espera un array en el lado derecho.

Tras inspeccionar el código, parece que el problema puede estar con el uso de la función “LOWER” en las columnas “student_name” y “subject”. Dado que la entrada es un array de cadenas de texto, se está aplicando la función “LOWER” a todo el array, lo cual genera un error cuando la cláusula WHERE intenta comparar el array en el lado izquierdo con una sola cadena en el lado derecho.

Para solucionar esto, la función “LOWER” debería aplicarse a cada elemento del array utilizando la función “UNNEST”. Aquí tienes una versión actualizada del código que debería funcionar:
```
CREATE OR REPLACE FUNCTION filtersearch(
    val1 text[] DEFAULT NULL::text[],
    val2 text[] DEFAULT NULL::text[],
    val3 text[] DEFAULT NULL::text[],
    val4 boolean DEFAULT NULL::boolean)
RETURNS SETOF student_table
LANGUAGE 'plpgsql'

AS $BODY$

DECLARE
   where_val text := '';
BEGIN

    IF $4 = true THEN
        where_val := ' AND attendance = true';
    END IF;

RETURN QUERY EXECUTE    
'SELECT * FROM student_table
    WHERE (
        SELECT UNNEST(LOWER(val1))
    ) = LOWER(student_name) OR val1 IS NULL
    AND (
        SELECT UNNEST(LOWER(val2))
    ) = LOWER(subject) OR val2 IS NULL
    AND (
        LOWER(comments) LIKE ANY(val3) OR val3 IS NULL
    )
    ' || where_val || '';
END 
$BODY$;
```
En esta versión actualizada, se utiliza la función “UNNEST” para recorrer cada elemento de los arrays de entrada, y la función “LOWER” se aplica a cada elemento individualmente. La cláusula WHERE también utiliza el operador “LIKE ANY” para comparar la columna “comments” con cada elemento del array “val3”.

Ahora, al llamar a la función con los datos de muestra dados, la siguiente consulta debería funcionar:
```
SELECT filtersearch(
    (array['Amelia', 'Charlotte']::text[]), 
    (NULL), 
    (NULL), 
    (true)
)
```

Comments are closed.

es.davy.ai

¿Tienes una pregunta?

Error en los parámetros plpgsql en la cláusula WHERE

Answer

davy.ai