Desinfecte la input del usuario con la palabra key USING en PL / pgSQL

Así es como creo mi search_term :

  IF char_length(search_term) > 0 THEN order_by := 'ts_rank_cd(textsearchable_index_col, to_tsquery(''' || search_term || ':*''))+GREATEST(0,(-1*EXTRACT(epoch FROM age(last_edited)/86400))+60)/60 DESC'; search_term := 'to_tsquery(''' || search_term || ':*'') @@ textsearchable_index_col'; ELSE search_term := 'true'; END IF; 

Estoy teniendo problemas con una function PLPGSQL:

  RETURN QUERY EXECUTE ' SELECT * FROM articles WHERE $1 AND ' || publication_date_query || ' AND primary_category LIKE ''' || category_filter || ''' AND ' || tags_query || ' AND ' || districts_query || ' AND ' || capability_query || ' AND ' || push_notification_query || ' AND ' || distance_query || ' AND ' || revision_by || ' AND ' || publication_priority_query || ' AND ' || status_query || ' AND is_template = ' || only_templates || ' AND status <> ''DELETED'' ORDER BY ' || order_by || ' LIMIT 500' USING search_term; END; $$; 

devuelve ERROR:

argumento de Y debe ser tipo boolean, no escribir text en el carácter 64

Opuesto a:

  RETURN QUERY EXECUTE ' SELECT * FROM articles WHERE ' || search_term || ' AND ' || publication_date_query || ' AND primary_category LIKE ''' || category_filter || ''' AND ' || tags_query || ' AND ' || districts_query || ' AND ' || capability_query || ' AND ' || push_notification_query || ' AND ' || distance_query || ' AND ' || revision_by || ' AND ' || publication_priority_query || ' AND ' || status_query || ' AND is_template = ' || only_templates || ' AND status <> ''DELETED'' ORDER BY ' || order_by || ' LIMIT 500'; END; $$; 

… que funciona ¿Me estoy perdiendo de algo?
Mi objective es desinfectar mi input de usuario.

Si algunos de sus parameters de input pueden ser NULL o estar vacíos y deben ignorarse en este caso, es mejor que construya su statement completa de forma dinámica dependiendo de la input del usuario, y omita por completo las respectivas cláusulas WHERE / ORDER BY .

La key es manejar NULL y cadena vacía correctamente, de manera segura (y elegante) en el process. Para empezar, search_term <> '' es una testing más inteligente que char_length(search_term) > 0 . Ver:

  • La mejor manera de verificar si hay "valor vacío o nulo"

Y necesita una comprensión firme de PL / pgSQL, o puede estar por encima de su cabeza. Código de ejemplo para su caso:

 CREATE OR REPLACE FUNCTION my_func( _search_term text = NULL -- default value NULL to allow short call , _publication_date_query date = NULL -- , more parameters ) RETURNS SETOF articles AS $func$ DECLARE sql text; sql_order text; -- defaults to NULL BEGIN sql := concat_ws(' AND ' ,'SELECT * FROM articles WHERE status <> ''DELETED''' -- first WHERE clause is immutable , CASE WHEN _search_term <> '' THEN '$1 @@ textsearchable_index_col' END -- ELSE NULL is implicit , CASE WHEN _publication_date_query <> '' THEN 'publication_date > $2' END -- or similar ... -- , more more parameters ); IF search_term <> '' THEN -- note use of $1! sql_order := 'ORDER BY ts_rank_cd(textsearchable_index_col, $1) + GREATEST(0,(-1*EXTRACT(epoch FROM age(last_edited)/86400))+60)/60 DESC'; END IF; RETURN QUERY EXECUTE concat_ws(' ', sql, sql_order, 'LIMIT 500') USING to_tsquery(_search_term || ':*') -- $1 -- prepare ts_query once here! , _publication_date_query -- $2 -- order of params must match! -- , more parameters ; END $func$ LANGUAGE plpgsql; 

Agregué valores pnetworkingeterminados para los parameters de la function, por lo que puede omitir los parameters que no se aplican en la llamada. Me gusta:

 SELECT * FROM my_func(_publication_date_query => '2016-01-01'); 

Más:

  • Funciones con número variable de parameters de input
  • El operador de asignación olvidado "=" y el lugar común ": ="

Tenga en count el uso estratégico de concat_ws() . Ver:

  • Cómo concatenar columnas en un Postgres SELECT?

Aquí hay una respuesta relacionada con muchas explicaciones:

  • Prueba de nulo en function con parameters variables