Re: Triggers and Full Text Search *

@Ericson,
Forgive me for seeming dense, but how does COPY help or hurt here?

@Andreas,
I had to laugh at your reference to "prose". Would you believe I am
actually a published playwright? Long before I started coding, of course.
Old habits die hard.....

entry_search_vector_trigger
BEGIN
SELECT setweight(to_tsvector(NEW.title), 'A') ||
setweight(to_tsvector(NEW.content), 'B') ||
setweight(to_tsvector(NEW.category), 'D') ||
setweight(to_tsvector(COALESCE(string_agg(tag.tag, ',
'), '')), 'C')
INTO NEW.search_vector
FROM ktab_entry AS entry
LEFT JOIN ktab_entry_tags AS entry_tags ON
entry_tags.entry_id = entry.id
LEFT JOIN ktab_tag AS tag ON tag.id = entry_tags.tag_id
WHERE entry.id = NEW.id
GROUP BY entry.id, category;
RETURN NEW;
END;

tag_search_vector_trigger
BEGIN
UPDATE ktab_entry SET id = id WHERE id IN (
SELECT entry_id FROM ktab_entry_tags WHERE tag_id = NEW.id
);
RETURN NEW;
END;

tags_search_vector_trigger
BEGIN
IF (TG_OP = 'DELETE') THEN
UPDATE ktab_entry SET id = id WHERE id = OLD.entry_id;
RETURN OLD;
ELSE
UPDATE ktab_entry SET id = id WHERE id = NEW.entry_id;
RETURN NEW;
END IF;
END;

search_vector_update
BEGIN
SELECT setweight(to_tsvector(NEW.title), 'A') ||
setweight(to_tsvector(NEW.content), 'B') ||
setweight(to_tsvector(NEW.category), 'D') ||
setweight(to_tsvector(COALESCE(string_agg(tag.tag, ',
'), '')), 'C')
INTO NEW.search_vector
FROM ktab_entry AS entry
LEFT JOIN ktab_entry_tags AS entry_tags ON
entry_tags.entry_id = entry.id
LEFT JOIN ktab_tag AS tag ON tag.id = entry_tags.tag_id
WHERE entry.id = NEW.id
GROUP BY entry.id, category;
RETURN NEW;
END;

search_vector_update (tags)
BEGIN
IF (TG_OP = 'DELETE') THEN
UPDATE ktab_entry SET id = id WHERE id = OLD.entry_id;
RETURN OLD;
ELSE
UPDATE ktab_entry SET id = id WHERE id = NEW.entry_id;
RETURN NEW;
END IF;
END;

Thank you!

*“None of you has faith until he loves for his brother or his neighbor what
he loves for himself.”*

On Tue, Apr 21, 2020 at 1:05 PM Ericson Smith <esconsult1(at)gmail(dot)com> wrote:

> I think COPY bypasses the triggers.
>
> Best Regards
> - Ericson Smith
> +1 876-375-9857 (whatsapp)
> +1 646-483-3420 (sms)
>
>
>
> On Wed, Apr 22, 2020 at 12:32 AM Andreas Joseph Krogh <andreas(at)visena(dot)com>
> wrote:
>
>> På tirsdag 21. april 2020 kl. 19:24:10, skrev Malik Rumi <
>> malik(dot)a(dot)rumi(at)gmail(dot)com>:
>>
>> [...]
>>
>> I am not (yet) posting the trigger code because this post is long
>> already, and if your answers are 1) yes, 2) no and 3) triggers often work /
>> fail like this, then there’s no point and we can wrap this up. But if not,
>> I will happily post what I have. Thank you.
>>
>>
>> This is too much prose for the regular programmer, show us the code, and
>> point out what doesn't work for you, then we can help:-)
>>
>> --
>> Andreas Joseph Krogh
>>
>