0
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff
changeset
|
1 |
/* -*- sql -*-
|
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff
changeset
|
2 |
|
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff
changeset
|
3 |
postgres specific registered procedures,
|
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff
changeset
|
4 |
require the plpgsql language installed
|
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff
changeset
|
5 |
|
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff
changeset
|
6 |
*/
|
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff
changeset
|
7 |
|
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff
changeset
|
8 |
CREATE FUNCTION comma_join (anyarray) RETURNS text AS $$
|
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff
changeset
|
9 |
SELECT array_to_string($1, ', ')
|
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff
changeset
|
10 |
$$ LANGUAGE SQL;;
|
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff
changeset
|
11 |
|
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff
changeset
|
12 |
CREATE AGGREGATE group_concat (
|
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff
changeset
|
13 |
basetype = anyelement,
|
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff
changeset
|
14 |
sfunc = array_append,
|
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff
changeset
|
15 |
stype = anyarray,
|
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff
changeset
|
16 |
finalfunc = comma_join,
|
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff
changeset
|
17 |
initcond = '{}'
|
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff
changeset
|
18 |
);;
|
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff
changeset
|
19 |
|
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff
changeset
|
20 |
|
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff
changeset
|
21 |
|
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff
changeset
|
22 |
CREATE FUNCTION limit_size (fulltext text, format text, maxsize integer) RETURNS text AS $$
|
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff
changeset
|
23 |
DECLARE
|
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff
changeset
|
24 |
plaintext text;
|
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff
changeset
|
25 |
BEGIN
|
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff
changeset
|
26 |
IF char_length(fulltext) < maxsize THEN
|
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff
changeset
|
27 |
RETURN fulltext;
|
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff
changeset
|
28 |
END IF;
|
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff
changeset
|
29 |
IF format = 'text/html' OR format = 'text/xhtml' OR format = 'text/xml' THEN
|
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff
changeset
|
30 |
plaintext := regexp_replace(fulltext, '<[\\w/][^>]+>', '', 'g');
|
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff
changeset
|
31 |
ELSE
|
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff
changeset
|
32 |
plaintext := fulltext;
|
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff
changeset
|
33 |
END IF;
|
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff
changeset
|
34 |
IF char_length(plaintext) < maxsize THEN
|
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff
changeset
|
35 |
RETURN plaintext;
|
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff
changeset
|
36 |
ELSE
|
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff
changeset
|
37 |
RETURN substring(plaintext from 1 for maxsize) || '...';
|
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff
changeset
|
38 |
END IF;
|
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff
changeset
|
39 |
END
|
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff
changeset
|
40 |
$$ LANGUAGE plpgsql;;
|
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff
changeset
|
41 |
|
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff
changeset
|
42 |
|
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff
changeset
|
43 |
CREATE FUNCTION text_limit_size (fulltext text, maxsize integer) RETURNS text AS $$
|
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff
changeset
|
44 |
BEGIN
|
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff
changeset
|
45 |
RETURN limit_size(fulltext, 'text/plain', maxsize);
|
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff
changeset
|
46 |
END
|
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff
changeset
|
47 |
$$ LANGUAGE plpgsql;;
|