schemas/_regproc.postgres.sql
author Julien Cristau <julien.cristau@logilab.fr>
Mon, 07 Apr 2014 12:28:25 +0200
changeset 9605 e6b8931abe17
parent 9419 3e6a1791339d
child 10327 a504a7840915
permissions -rw-r--r--
[sources] ensure we have a cnxset in undoable_transactions
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
4321
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4126
diff changeset
     1
/* -*- sql -*-
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
     2
4321
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4126
diff changeset
     3
   postgres specific registered procedures,
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4126
diff changeset
     4
   require the plpgsql language installed
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
     5
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
     6
*/
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
     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
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
     9
CREATE FUNCTION comma_join (anyarray) RETURNS text AS $$
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    10
    SELECT array_to_string($1, ', ')
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    11
$$ LANGUAGE SQL;;
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    12
9364
73bd5012336f Make the GROUP_CONCAT aggregate function not repeat values (closes #3223975)
Julien Cristau <julien.cristau@logilab.fr>
parents: 4321
diff changeset
    13
73bd5012336f Make the GROUP_CONCAT aggregate function not repeat values (closes #3223975)
Julien Cristau <julien.cristau@logilab.fr>
parents: 4321
diff changeset
    14
CREATE FUNCTION cw_array_append_unique (anyarray, anyelement) RETURNS anyarray AS $$
73bd5012336f Make the GROUP_CONCAT aggregate function not repeat values (closes #3223975)
Julien Cristau <julien.cristau@logilab.fr>
parents: 4321
diff changeset
    15
    SELECT array_append($1, (SELECT $2 WHERE $2 <> ALL($1)))
9419
3e6a1791339d [schema/stored procs] fix syntax error (closes #3450362)
Aurelien Campeas <aurelien.campeas@logilab.fr>
parents: 9364
diff changeset
    16
$$ LANGUAGE SQL;;
9364
73bd5012336f Make the GROUP_CONCAT aggregate function not repeat values (closes #3223975)
Julien Cristau <julien.cristau@logilab.fr>
parents: 4321
diff changeset
    17
4126
2eb47c1efee1 make cubicweb-ctl db-init -d work with postgresql backend
Alexandre Fayolle <alexandre.fayolle@logilab.fr>
parents: 3928
diff changeset
    18
DROP AGGREGATE IF EXISTS group_concat (anyelement) CASCADE;
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    19
CREATE AGGREGATE group_concat (
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    20
  basetype = anyelement,
9364
73bd5012336f Make the GROUP_CONCAT aggregate function not repeat values (closes #3223975)
Julien Cristau <julien.cristau@logilab.fr>
parents: 4321
diff changeset
    21
  sfunc = cw_array_append_unique,
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    22
  stype = anyarray,
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    23
  finalfunc = comma_join,
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    24
  initcond = '{}'
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    25
);;
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    26
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    27
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    28
4126
2eb47c1efee1 make cubicweb-ctl db-init -d work with postgresql backend
Alexandre Fayolle <alexandre.fayolle@logilab.fr>
parents: 3928
diff changeset
    29
DROP FUNCTION IF EXISTS limit_size (fulltext text, format text, maxsize integer);
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    30
CREATE FUNCTION limit_size (fulltext text, format text, maxsize integer) RETURNS text AS $$
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    31
DECLARE
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    32
    plaintext text;
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    33
BEGIN
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    34
    IF char_length(fulltext) < maxsize THEN
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    35
       RETURN fulltext;
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    36
    END IF;
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    37
    IF format = 'text/html' OR format = 'text/xhtml' OR format = 'text/xml' THEN
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    38
       plaintext := regexp_replace(fulltext, '<[\\w/][^>]+>', '', 'g');
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    39
    ELSE
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    40
       plaintext := fulltext;
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    41
    END IF;
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    42
    IF char_length(plaintext) < maxsize THEN
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    43
       RETURN plaintext;
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    44
    ELSE
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    45
       RETURN substring(plaintext from 1 for maxsize) || '...';
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    46
    END IF;
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    47
END
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    48
$$ LANGUAGE plpgsql;;
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    49
4126
2eb47c1efee1 make cubicweb-ctl db-init -d work with postgresql backend
Alexandre Fayolle <alexandre.fayolle@logilab.fr>
parents: 3928
diff changeset
    50
DROP FUNCTION IF EXISTS text_limit_size (fulltext text, maxsize integer);
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    51
CREATE FUNCTION text_limit_size (fulltext text, maxsize integer) RETURNS text AS $$
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    52
BEGIN
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    53
    RETURN limit_size(fulltext, 'text/plain', maxsize);
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    54
END
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    55
$$ LANGUAGE plpgsql;;