schemas/_regproc.postgres.sql
author Sylvain Thénault <sylvain.thenault@logilab.fr>
Sun, 16 Jun 2013 23:36:06 +0200
changeset 9446 18a186b02970
parent 9364 73bd5012336f
child 9419 3e6a1791339d
permissions -rw-r--r--
[multi-sources-removal] Move cw.server.sources.ConnectionWrapper where it belongs It's only used by the ldap utilities. Related to #2919300
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)))
73bd5012336f Make the GROUP_CONCAT aggregate function not repeat values (closes #3223975)
Julien Cristau <julien.cristau@logilab.fr>
parents: 4321
diff changeset
    16
$$ LANGUAGE SQL
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;;