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;;