@Saurabh Nanda:与您发布的内容类似,您还可以创建一个简单的函数将 varchar 数组转换为小写,如下所示:
CREATE OR REPLACE FUNCTION array_lowercase(varchar[]) RETURNS varchar[] AS
$BODY$
SELECT array_agg(q.tag) FROM (
SELECT btrim(lower(unnest($1)))::varchar AS tag
) AS q;
$BODY$
language sql IMMUTABLE;
请注意,我还修剪了空格标签。这对你来说可能不是必需的,但我通常这样做是为了保持一致性。
Testing:
SELECT array_lowercase(array['Hello','WOrLD']);
array_lowercase
-----------------
{hello,world}
(1 row)
正如 Saurabh 所指出的,您可以创建一个 GIN 索引:
CREATE INDEX ix_tags ON tagtable USING GIN(array_lowercase(tags));
并查询:
SELECT * FROM tagtable WHERE ARRAY['mytag'::varchar] && array_lowercase(tags);
UPDATE:的表演WHILE
与 array_agg/unnest 比较
我创建了 100K 10 元素的表text[]
数组(12 个字符的随机混合大小写字符串)并测试每个函数。
array_agg/unnest 函数返回:
EXPLAIN ANALYZE VERBOSE SELECT array_lowercase(data) FROM test;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
Seq Scan on public.test (cost=0.00..28703.00 rows=100000 width=184) (actual time=0.320..3041.292 rows=100000 loops=1)
Output: array_lowercase((data)::character varying[])
Total runtime: 3174.690 ms
(3 rows)
WHILE 函数返回:
EXPLAIN ANALYZE VERBOSE SELECT array_lowercase_while(data) FROM test;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
Seq Scan on public.test (cost=0.00..28703.00 rows=100000 width=184) (actual time=5.128..4356.647 rows=100000 loops=1)
Output: array_lowercase_while((data)::character varying[])
Total runtime: 4485.226 ms
(3 rows)
更新2: FOREACH
vs. WHILE
作为最后的实验,我将 WHILE 函数更改为使用 FOREACH:
CREATE OR REPLACE FUNCTION array_lowercase_foreach(p_input varchar[]) RETURNS varchar[] AS $BODY$
DECLARE
el text;
r varchar[];
BEGIN
FOREACH el IN ARRAY p_input LOOP
r := r || btrim(lower(el))::varchar;
END LOOP;
RETURN r;
END;
$BODY$
language 'plpgsql'
结果似乎类似于WHILE
:
EXPLAIN ANALYZE VERBOSE SELECT array_lowercase_foreach(data) FROM test;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
Seq Scan on public.test (cost=0.00..28703.00 rows=100000 width=184) (actual time=0.707..4106.867 rows=100000 loops=1)
Output: array_lowercase_foreach((data)::character varying[])
Total runtime: 4239.958 ms
(3 rows)
尽管我的测试并不严格,但我确实多次运行每个版本并发现这些数字具有代表性,这表明 SQL 方法 (array_agg/unnest) 是最快的。