asset.copy_tag

↑ asset 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: config.copy_tag_type.

Columns

Column Type Nullable Default Notes

id PK

integer

No

nextval('asset.copy_tag_id_seq'::regclass)

tag_type FK

text

Yes

→ config.copy_tag_type(code)

label

text

No

value

text

No

index_vector

tsvector

No

staff_note

text

Yes

pub

boolean

Yes

true

owner FK

integer

No

→ actor.org_unit(id)

url

text

Yes

Primary Key

(id)

Foreign Keys

Column(s) References On Delete On Update Deferrable Constraint

owner

actor.org_unit(id)

NO ACTION

NO ACTION

No

copy_tag_owner_fkey

tag_type

config.copy_tag_type(code)

CASCADE

CASCADE

No

copy_tag_tag_type_fkey

Indexes

Index Method Definition

copy_tag_pkey PK

btree

CREATE UNIQUE INDEX copy_tag_pkey ON asset.copy_tag USING btree (id)

asset_copy_tag_index_vector_idx

GIN

CREATE INDEX asset_copy_tag_index_vector_idx ON asset.copy_tag USING gin (index_vector)

asset_copy_tag_label_idx

btree

CREATE INDEX asset_copy_tag_label_idx ON asset.copy_tag USING btree (label)

asset_copy_tag_label_lower_idx

btree

CREATE INDEX asset_copy_tag_label_lower_idx ON asset.copy_tag USING btree (lowercase(label))

asset_copy_tag_owner_idx

btree

CREATE INDEX asset_copy_tag_owner_idx ON asset.copy_tag USING btree (owner)

asset_copy_tag_tag_type_idx

btree

CREATE INDEX asset_copy_tag_tag_type_idx ON asset.copy_tag USING btree (tag_type)

Triggers

Trigger Timing Event Level Function

asset_copy_tag_do_value

BEFORE

INSERT OR UPDATE

ROW

asset.set_copy_tag_value()

asset_copy_tag_fti_trigger

BEFORE

INSERT OR UPDATE

ROW

public.oils_tsearch2()

Trigger Bodies

asset_copy_tag_do_value

Function: asset.set_copy_tag_value()
Timing: BEFORE INSERT OR UPDATE ROW

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

 
BEGIN
    IF NEW.value IS NULL THEN
        NEW.value = NEW.label;
    END IF;

    RETURN NEW;
END;

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

Referenced By

The following tables have foreign keys pointing to asset.copy_tag (1 referencing table(s)):

Table Referencing Column(s) Referenced Column(s) Constraint

asset.copy_tag_copy_map

tag

id

copy_tag_copy_map_tag_fkey
Previous
Next