metabib.real_full_rec

↑ metabib schema

Data-Modifying Triggers: This table has BEFORE ROW trigger(s) that modify row data before write. Values you INSERT or UPDATE may differ from what is actually stored. See the Triggers section below.

Cascading Deletes: Deleting rows from this table will cascade to: biblio.record_entry.

Deferrable Constraints: The following FK constraints are deferrable — they are checked at transaction end, not statement end: metabib_full_rec_record_fkey.

Columns

Column Type Nullable Default Notes

id PK

bigint

No

nextval('metabib.full_rec_id_seq'::regclass)

record FK

bigint

No

→ biblio.record_entry(id)

tag

character(3)

No

ind1

text

Yes

ind2

text

Yes

subfield

text

Yes

value

text

No

index_vector

tsvector

No

Primary Key

(id)

Foreign Keys

Column(s) References On Delete On Update Deferrable Constraint

record

biblio.record_entry(id)

CASCADE

NO ACTION

DEFERRED

metabib_full_rec_record_fkey

Indexes

Index Method Definition

real_full_rec_pkey PK

btree

CREATE UNIQUE INDEX real_full_rec_pkey ON metabib.real_full_rec USING btree (id)

metabib_full_rec_02x_tag_subfield_lower_substring

btree

CREATE INDEX metabib_full_rec_02x_tag_subfield_lower_substring ON metabib.real_full_rec USING btree (tag, subfield, lower("substring"(value, 1, 1024))) WHERE (tag = ANY (ARRAY['020'::bpchar, '022'::bpchar, '024'::bpchar]))

metabib_full_rec_index_vector_idx

GIN

CREATE INDEX metabib_full_rec_index_vector_idx ON metabib.real_full_rec USING gin (index_vector)

metabib_full_rec_isxn_caseless_idx

btree

CREATE INDEX metabib_full_rec_isxn_caseless_idx ON metabib.real_full_rec USING btree (lower(value)) WHERE (tag = ANY (ARRAY['020'::bpchar, '022'::bpchar, '024'::bpchar]))

metabib_full_rec_record_idx

btree

CREATE INDEX metabib_full_rec_record_idx ON metabib.real_full_rec USING btree (record)

metabib_full_rec_tag_subfield_idx

btree

CREATE INDEX metabib_full_rec_tag_subfield_idx ON metabib.real_full_rec USING btree (tag, subfield)

metabib_full_rec_value_idx

btree

CREATE INDEX metabib_full_rec_value_idx ON metabib.real_full_rec USING btree ("substring"(value, 1, 1024))

metabib_full_rec_value_tpo_index

btree

CREATE INDEX metabib_full_rec_value_tpo_index ON metabib.real_full_rec USING btree ("substring"(value, 1, 1024) text_pattern_ops)

Triggers

Trigger Timing Event Level Function

metabib_full_rec_fti_trigger

BEFORE

INSERT OR UPDATE

ROW

public.oils_tsearch2()

Trigger Bodies

metabib_full_rec_fti_trigger

Function: public.oils_tsearch2()
Timing: BEFORE INSERT OR UPDATE ROW

This trigger modifies the row before it is written (returns a modified NEW).

 
DECLARE
    normalizer      RECORD;
    value           TEXT := '';
    temp_vector     TEXT := '';
    ts_rec          RECORD;
    cur_weight      "char";
BEGIN

    value := NEW.value;
    NEW.index_vector = ''::tsvector;

    IF TG_TABLE_NAME::TEXT ~ 'field_entry$' THEN
        FOR normalizer IN
            SELECT  n.func AS func,
                    n.param_count AS param_count,
                    m.params AS params
              FROM  config.index_normalizer n
                    JOIN config.metabib_field_index_norm_map m ON (m.norm = n.id)
              WHERE field = NEW.field AND m.pos < 0
              ORDER BY m.pos LOOP
                EXECUTE 'SELECT ' || normalizer.func || '(' ||
                    quote_literal( value ) ||
                    CASE
                        WHEN normalizer.param_count > 0
                            THEN ',' || REPLACE(REPLACE(BTRIM(normalizer.params,'[]'),E'\'',E'\\\''),E'"',E'\'')
                            ELSE ''
                        END ||
                    ')' INTO value;

        END LOOP;

        NEW.value = value;

        FOR normalizer IN
            SELECT  n.func AS func,
                    n.param_count AS param_count,
                    m.params AS params
              FROM  config.index_normalizer n
                    JOIN config.metabib_field_index_norm_map m ON (m.norm = n.id)
              WHERE field = NEW.field AND m.pos >= 0
              ORDER BY m.pos LOOP
                EXECUTE 'SELECT ' || normalizer.func || '(' ||
                    quote_literal( value ) ||
                    CASE
                        WHEN normalizer.param_count > 0
                            THEN ',' || REPLACE(REPLACE(BTRIM(normalizer.params,'[]'),E'\'',E'\\\''),E'"',E'\'')
                            ELSE ''
                        END ||
                    ')' INTO value;

        END LOOP;
   END IF;

    IF TG_TABLE_NAME::TEXT ~ 'browse_entry$' THEN

        value :=  ARRAY_TO_STRING(
            evergreen.regexp_split_to_array(value, E'\\W+'), ' '
        );
        value := public.search_normalize(value);
        NEW.index_vector = to_tsvector(TG_ARGV[0]::regconfig, value);

    ELSIF TG_TABLE_NAME::TEXT ~ 'field_entry$' THEN
        FOR ts_rec IN

            SELECT DISTINCT m.ts_config, m.index_weight
            FROM config.metabib_class_ts_map m
                 LEFT JOIN metabib.record_attr_vector_list r ON (r.source = NEW.source)
                 LEFT JOIN config.coded_value_map ccvm ON (
                    ccvm.ctype IN ('item_lang', 'language') AND
                    ccvm.code = m.index_lang AND
                    r.vlist @> intset(ccvm.id)
                )
            WHERE m.field_class = TG_ARGV[0]
                AND m.active
                AND (m.always OR NOT EXISTS (SELECT 1 FROM config.metabib_field_ts_map WHERE metabib_field = NEW.field))
                AND (m.index_lang IS NULL OR ccvm.id IS NOT NULL)
                        UNION
            SELECT DISTINCT m.ts_config, m.index_weight
            FROM config.metabib_field_ts_map m
                 LEFT JOIN metabib.record_attr_vector_list r ON (r.source = NEW.source)
                 LEFT JOIN config.coded_value_map ccvm ON (
                    ccvm.ctype IN ('item_lang', 'language') AND
                    ccvm.code = m.index_lang AND
                    r.vlist @> intset(ccvm.id)
                )
            WHERE m.metabib_field = NEW.field
                AND m.active
                AND (m.index_lang IS NULL OR ccvm.id IS NOT NULL)
            ORDER BY index_weight ASC

        LOOP

            IF cur_weight IS NOT NULL AND cur_weight != ts_rec.index_weight THEN
                NEW.index_vector = NEW.index_vector || setweight(temp_vector::tsvector,cur_weight);
                temp_vector = '';
            END IF;

            cur_weight = ts_rec.index_weight;
            SELECT INTO temp_vector temp_vector || ' ' || to_tsvector(ts_rec.ts_config::regconfig, value)::TEXT;

        END LOOP;
        NEW.index_vector = NEW.index_vector || setweight(temp_vector::tsvector,cur_weight);
    ELSE
        NEW.index_vector = to_tsvector(TG_ARGV[0]::regconfig, value);
    END IF;

    RETURN NEW;
END;
Previous
Next