author | Sylvain Thénault <sylvain.thenault@logilab.fr> |
Thu, 06 May 2010 11:30:46 +0200 | |
changeset 5489 | 50e2d8e10638 |
parent 4321 | 80b455066c9a |
child 9364 | 73bd5012336f |
permissions | -rw-r--r-- |
4321 | 1 |
/* -*- sql -*- |
0 | 2 |
|
4321 | 3 |
postgres specific registered procedures, |
4 |
require the plpgsql language installed |
|
0 | 5 |
|
6 |
*/ |
|
7 |
||
4126
2eb47c1efee1
make cubicweb-ctl db-init -d work with postgresql backend
Alexandre Fayolle <alexandre.fayolle@logilab.fr>
parents:
3928
diff
changeset
|
8 |
DROP FUNCTION IF EXISTS comma_join (anyarray) CASCADE; |
0 | 9 |
CREATE FUNCTION comma_join (anyarray) RETURNS text AS $$ |
10 |
SELECT array_to_string($1, ', ') |
|
11 |
$$ LANGUAGE SQL;; |
|
12 |
||
4126
2eb47c1efee1
make cubicweb-ctl db-init -d work with postgresql backend
Alexandre Fayolle <alexandre.fayolle@logilab.fr>
parents:
3928
diff
changeset
|
13 |
DROP AGGREGATE IF EXISTS group_concat (anyelement) CASCADE; |
0 | 14 |
CREATE AGGREGATE group_concat ( |
15 |
basetype = anyelement, |
|
16 |
sfunc = array_append, |
|
17 |
stype = anyarray, |
|
18 |
finalfunc = comma_join, |
|
19 |
initcond = '{}' |
|
20 |
);; |
|
21 |
||
22 |
||
23 |
||
4126
2eb47c1efee1
make cubicweb-ctl db-init -d work with postgresql backend
Alexandre Fayolle <alexandre.fayolle@logilab.fr>
parents:
3928
diff
changeset
|
24 |
DROP FUNCTION IF EXISTS limit_size (fulltext text, format text, maxsize integer); |
0 | 25 |
CREATE FUNCTION limit_size (fulltext text, format text, maxsize integer) RETURNS text AS $$ |
26 |
DECLARE |
|
27 |
plaintext text; |
|
28 |
BEGIN |
|
29 |
IF char_length(fulltext) < maxsize THEN |
|
30 |
RETURN fulltext; |
|
31 |
END IF; |
|
32 |
IF format = 'text/html' OR format = 'text/xhtml' OR format = 'text/xml' THEN |
|
33 |
plaintext := regexp_replace(fulltext, '<[\\w/][^>]+>', '', 'g'); |
|
34 |
ELSE |
|
35 |
plaintext := fulltext; |
|
36 |
END IF; |
|
37 |
IF char_length(plaintext) < maxsize THEN |
|
38 |
RETURN plaintext; |
|
39 |
ELSE |
|
40 |
RETURN substring(plaintext from 1 for maxsize) || '...'; |
|
41 |
END IF; |
|
42 |
END |
|
43 |
$$ LANGUAGE plpgsql;; |
|
44 |
||
4126
2eb47c1efee1
make cubicweb-ctl db-init -d work with postgresql backend
Alexandre Fayolle <alexandre.fayolle@logilab.fr>
parents:
3928
diff
changeset
|
45 |
DROP FUNCTION IF EXISTS text_limit_size (fulltext text, maxsize integer); |
0 | 46 |
CREATE FUNCTION text_limit_size (fulltext text, maxsize integer) RETURNS text AS $$ |
47 |
BEGIN |
|
48 |
RETURN limit_size(fulltext, 'text/plain', maxsize); |
|
49 |
END |
|
50 |
$$ LANGUAGE plpgsql;; |