util_search_suggestions

Columns

Column

Type

Size

Nulls

Auto

Default

Children

Parents

Comments

prefix

text

2147483647

√

null

pdbid

int4

√

null

is_student

bool

√

null

is_staff

bool

√

null

search_type

text

2147483647

√

null

Table contained 0 rows

View Definition

WITH people AS (
         SELECT w.pdbid,
            lower(w.surname) AS surname,
            (w.uw_id)::text AS uw_id,
            lower(w.userid) AS userid,
            ((w.uw_id IS NOT NULL) AND (std_student.uw_id IS NOT NULL)) AS is_student,
            ((w.hr_id IS NOT NULL) OR (w.userid = 'cs-oat'::text)) AS is_staff
           FROM (_watiam.watiam_entry w
             LEFT JOIN _quest.std_student USING (uw_id))
          WHERE ((NOT w.expired) AND ((w.uw_id IS NOT NULL) OR (w.hr_id IS NOT NULL) OR (w.userid = 'cs-oat'::text)))
        )
 SELECT names.prefix,
    names.pdbid,
    names.is_student,
    names.is_staff,
    names.search_type
   FROM ( SELECT foo.prefix,
            foo.pdbid,
            foo.is_student,
            foo.is_staff,
            'name'::text AS search_type
           FROM ( SELECT "left"(people.surname, i.i) AS prefix,
                    people.pdbid,
                    people.is_student,
                    people.is_staff,
                    row_number() OVER (PARTITION BY ("left"(people.surname, i.i)) ORDER BY (random())) AS "row"
                   FROM people,
                    LATERAL generate_series(1, length(people.surname)) i(i)
                  WHERE (length(people.surname) >= i.i)) foo
          WHERE (foo."row" <= 20)) names
UNION
 SELECT foo.prefix,
    foo.pdbid,
    foo.is_student,
    foo.is_staff,
    'uw_id'::text AS search_type
   FROM ( SELECT "left"(people.uw_id, i.i) AS prefix,
            people.pdbid,
            people.is_student,
            people.is_staff,
            row_number() OVER (PARTITION BY ("left"(people.uw_id, i.i)) ORDER BY (random())) AS "row"
           FROM people,
            generate_series(1, 8) i(i)
          WHERE (length(people.uw_id) >= i.i)) foo
  WHERE (foo."row" <= 20)
UNION
 SELECT foo.prefix,
    foo.pdbid,
    foo.is_student,
    foo.is_staff,
    'userid'::text AS search_type
   FROM ( SELECT "left"(people.userid, i.i) AS prefix,
            people.pdbid,
            people.is_student,
            people.is_staff,
            row_number() OVER (PARTITION BY ("left"(people.userid, i.i)) ORDER BY (random())) AS "row"
           FROM people,
            LATERAL generate_series(1, length(people.userid)) i(i)
          WHERE (length(people.userid) >= i.i)) foo
  WHERE (foo."row" <= 20);

Description

Columns

View Definition

Possibly Referenced Tables/Views