I created a function used for filtering

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$;

I am having an error in calling it,

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

Any suggestions or comments in my code is deeply appreciated

Here's the sample data for my table

I am calling the code like this

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

CodePudding user response：

The $1, $2, $3 placeholders inside the SQL string reference the parameters passed through the USING clause of the EXECUTE QUERY command. You are only passing one argument which is either '' or the string 'attendance = true' - neither can be used for an IN condition.

So you need to pass the three parameters with the USING clause, but not the string that you append to the SQL query. If you are using ILIKE there is no need to use lower() with that condition. Your where_val string also missing an AND (or an OR)

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';
    raise notice 'Value: %', where_val;
  END IF;
    
  RETURN QUERY EXECUTE    
   'SELECT * 
    FROM student_table
      WHERE (LOWER(student_name) = ANY($1) OR $1 IS NULL)
      AND (LOWER(subject") = ANY ($2) OR $2 IS NULL)
      AND (LOWER("comments") ILIKE ANY($3) OR $3 IS NULL)
     '|| where_val
  USING val1, val2, val3; -- do NOT pass where_val here
END 
$BODY$;

You don't really need dynamic SQL for this. You can apply the same logic as you tried with the first 3 parameters for the boolean parameter.

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$
BEGIN

  RETURN QUERY
    SELECT * 
    FROM student_table
      WHERE (LOWER(student_name) = any(val1) OR val1 IS NULL)
        AND (LOWER(subject) = any(val2) OR val2 IS NULL)
        AND (comments ILIKE ANY(val3) OR val3 IS NULL)
        AND (attendance = val4 or val4 = false or val4 is null);
END 
$BODY$;

You wouldn't even need PL/pgSQL for the above.