postgresql - 高效过滤类型忽略

Question

我有一组数据(来自不同表的不同类型的数十列)，我想根据用户的输入过滤它们。

所以我有一个 postgress 函数，它有两个参数，一个是文本数组，它将有用户输入(字符串数组)，另一个 bool 值，它指示是否所有这些都应该在里面where 子句。

• 第一个问题是:

  如果我们有很多列要与它们进行比较，那么我们将有一个又大又丑的 where 条件!有没有其他更好的情况，我们可以将它们添加到一个大数组中并将两个数组放在一起比较，或者从另一个函数调用它们？

输入示例以及结果应如何匹配:

关键字:Agent AND Mark AND 27

数据:位置 |姓名 |年龄

15423 |房地产经纪人 |马克·米勒 | 27 --> 3/3 职位、姓名、年龄 --> TRUE

2754 | secret 特工 |马库斯·史密斯 | 43 --> 3/3 in id, position, name --> TRUE

4567 |设施代理 |山姆·威尔逊 | 55 --> 1/3 位置 --> FALSE

postgresql 函数:

 CREATE OR REPLACE FUNCTION public.filter(
"FilterText" text[],
"trueORFalse" boolean)
 RETURNS record AS
 $BODY$

select DISTINCT t1.id,
t2.position ,
t3.name ,
t4 age

FROM table1 AS t1 
LEFT JOIN  table2 AS t2 on t1.id = t2.fk_id 
LEFT JOIN  table3 AS t3  on t3.fk_id = t2.fk_id 
LEFT JOIN  table4 AS t4  on t4.fk_id = t3.fk_id 
WHERE 
t2.position like ANY($1) 
   -- (AND - OR) depending on the second paramater value $2 ?           
t3.name like ANY($1) 
   -- (AND - OR) depending on the second paramater value $2 ?           
t4 age like ANY($1) 

$BODY$
LANGUAGE sql STABLE SECURITY DEFINER
 COST 100;
 ALTER FUNCTION public.filter(text[], boolean)
 OWNER TO table_rights_in_functions;
 GRANT EXECUTE ON FUNCTION public.filter(text[], boolean) TO public;
 GRANT EXECUTE ON FUNCTION public.filter( text[], boolean) TO 
 table_rights_in_functions;

• 另一个问题是:

  如何添加 CASE WHEN 条件来检查第二个参数确定 where 子句中的 AND - OR 情况？

最后我的postgres版本是9.6.8

Answer 1

我发现了这两个神奇的函数:to_tsvector、to_tsquery，它们比较两个字符串数组并用于控制文本搜索。更多信息: https://www.postgresql.org/docs/9.6/static/textsearch-controls.html

例如

我们必须创建新函数:

  CREATE OR REPLACE FUNCTION f_concat_ws(text, VARIADIC text[])
  RETURNS text LANGUAGE sql IMMUTABLE AS 'SELECT array_to_string($2, $1)';

然后在 where 子句中:

  WHERE  to_tsvector('simple', f_concat_ws(' ', t2.position,t3.name,t4.age))
  @@ to_tsquery('simple', $2);