1 /* -*- sql -*- |
|
2 |
|
3 postgres specific registered procedures, |
|
4 require the plpgsql language installed |
|
5 |
|
6 */ |
|
7 |
|
8 DROP FUNCTION IF EXISTS comma_join (anyarray) CASCADE; |
|
9 CREATE FUNCTION comma_join (anyarray) RETURNS text AS $$ |
|
10 SELECT array_to_string($1, ', ') |
|
11 $$ LANGUAGE SQL;; |
|
12 |
|
13 |
|
14 DROP FUNCTION IF EXISTS cw_array_append_unique (anyarray, anyelement) CASCADE; |
|
15 CREATE FUNCTION cw_array_append_unique (anyarray, anyelement) RETURNS anyarray AS $$ |
|
16 SELECT array_append($1, (SELECT $2 WHERE $2 <> ALL($1))) |
|
17 $$ LANGUAGE SQL;; |
|
18 |
|
19 DROP AGGREGATE IF EXISTS group_concat (anyelement) CASCADE; |
|
20 CREATE AGGREGATE group_concat ( |
|
21 basetype = anyelement, |
|
22 sfunc = cw_array_append_unique, |
|
23 stype = anyarray, |
|
24 finalfunc = comma_join, |
|
25 initcond = '{}' |
|
26 );; |
|
27 |
|
28 |
|
29 DROP FUNCTION IF EXISTS limit_size (fulltext text, format text, maxsize integer); |
|
30 CREATE FUNCTION limit_size (fulltext text, format text, maxsize integer) RETURNS text AS $$ |
|
31 DECLARE |
|
32 plaintext text; |
|
33 BEGIN |
|
34 IF char_length(fulltext) < maxsize THEN |
|
35 RETURN fulltext; |
|
36 END IF; |
|
37 IF format = 'text/html' OR format = 'text/xhtml' OR format = 'text/xml' THEN |
|
38 plaintext := regexp_replace(fulltext, '<[a-zA-Z/][^>]*>', '', 'g'); |
|
39 ELSE |
|
40 plaintext := fulltext; |
|
41 END IF; |
|
42 IF char_length(plaintext) < maxsize THEN |
|
43 RETURN plaintext; |
|
44 ELSE |
|
45 RETURN substring(plaintext from 1 for maxsize) || '...'; |
|
46 END IF; |
|
47 END |
|
48 $$ LANGUAGE plpgsql;; |
|
49 |
|
50 DROP FUNCTION IF EXISTS text_limit_size (fulltext text, maxsize integer); |
|
51 CREATE FUNCTION text_limit_size (fulltext text, maxsize integer) RETURNS text AS $$ |
|
52 BEGIN |
|
53 RETURN limit_size(fulltext, 'text/plain', maxsize); |
|
54 END |
|
55 $$ LANGUAGE plpgsql;; |
|