1 /* -*- sql -*- 

     2 

     3    postgres specific registered procedures, 

     4    require the plpgsql language installed 

     5 

     6 */

     7 

     8 CREATE FUNCTION comma_join (anyarray) RETURNS text AS $$

     9     SELECT array_to_string($1, ', ')

    10 $$ LANGUAGE SQL;;

    11 

    12 CREATE AGGREGATE group_concat (

    13   basetype = anyelement,

    14   sfunc = array_append,

    15   stype = anyarray,

    16   finalfunc = comma_join,

    17   initcond = '{}'

    18 );;

    19 

    20 

    21 

    22 CREATE FUNCTION limit_size (fulltext text, format text, maxsize integer) RETURNS text AS $$

    23 DECLARE

    24     plaintext text;

    25 BEGIN

    26     IF char_length(fulltext) < maxsize THEN

    27        RETURN fulltext;

    28     END IF;

    29     IF format = 'text/html' OR format = 'text/xhtml' OR format = 'text/xml' THEN

    30        plaintext := regexp_replace(fulltext, '<[\\w/][^>]+>', '', 'g');

    31     ELSE

    32        plaintext := fulltext;

    33     END IF;

    34     IF char_length(plaintext) < maxsize THEN

    35        RETURN plaintext;

    36     ELSE

    37        RETURN substring(plaintext from 1 for maxsize) || '...';

    38     END IF;

    39 END

    40 $$ LANGUAGE plpgsql;;

    41 

    42 

    43 CREATE FUNCTION text_limit_size (fulltext text, maxsize integer) RETURNS text AS $$

    44 BEGIN

    45     RETURN limit_size(fulltext, 'text/plain', maxsize);

    46 END

    47 $$ LANGUAGE plpgsql;;