3 postgres specific registered procedures, |
3 postgres specific registered procedures, |
4 require the plpgsql language installed |
4 require the plpgsql language installed |
5 |
5 |
6 */ |
6 */ |
7 |
7 |
|
8 DROP FUNCTION IF EXISTS comma_join (anyarray) CASCADE; |
8 CREATE FUNCTION comma_join (anyarray) RETURNS text AS $$ |
9 CREATE FUNCTION comma_join (anyarray) RETURNS text AS $$ |
9 SELECT array_to_string($1, ', ') |
10 SELECT array_to_string($1, ', ') |
10 $$ LANGUAGE SQL;; |
11 $$ LANGUAGE SQL;; |
11 |
12 |
|
13 DROP AGGREGATE IF EXISTS group_concat (anyelement) CASCADE; |
12 CREATE AGGREGATE group_concat ( |
14 CREATE AGGREGATE group_concat ( |
13 basetype = anyelement, |
15 basetype = anyelement, |
14 sfunc = array_append, |
16 sfunc = array_append, |
15 stype = anyarray, |
17 stype = anyarray, |
16 finalfunc = comma_join, |
18 finalfunc = comma_join, |
17 initcond = '{}' |
19 initcond = '{}' |
18 );; |
20 );; |
19 |
21 |
20 |
22 |
21 |
23 |
|
24 DROP FUNCTION IF EXISTS limit_size (fulltext text, format text, maxsize integer); |
22 CREATE FUNCTION limit_size (fulltext text, format text, maxsize integer) RETURNS text AS $$ |
25 CREATE FUNCTION limit_size (fulltext text, format text, maxsize integer) RETURNS text AS $$ |
23 DECLARE |
26 DECLARE |
24 plaintext text; |
27 plaintext text; |
25 BEGIN |
28 BEGIN |
26 IF char_length(fulltext) < maxsize THEN |
29 IF char_length(fulltext) < maxsize THEN |
37 RETURN substring(plaintext from 1 for maxsize) || '...'; |
40 RETURN substring(plaintext from 1 for maxsize) || '...'; |
38 END IF; |
41 END IF; |
39 END |
42 END |
40 $$ LANGUAGE plpgsql;; |
43 $$ LANGUAGE plpgsql;; |
41 |
44 |
42 |
45 DROP FUNCTION IF EXISTS text_limit_size (fulltext text, maxsize integer); |
43 CREATE FUNCTION text_limit_size (fulltext text, maxsize integer) RETURNS text AS $$ |
46 CREATE FUNCTION text_limit_size (fulltext text, maxsize integer) RETURNS text AS $$ |
44 BEGIN |
47 BEGIN |
45 RETURN limit_size(fulltext, 'text/plain', maxsize); |
48 RETURN limit_size(fulltext, 'text/plain', maxsize); |
46 END |
49 END |
47 $$ LANGUAGE plpgsql;; |
50 $$ LANGUAGE plpgsql;; |