Re: Triggers and Full Text Search *

My apologies - I did not look closely at the manual. Many many years ago
(6.xx days I had a similar problem and leapt to answer).

Could you post your CREATE TRIGGER statements as well?

On Wed, Apr 22, 2020 at 1:21 AM Malik Rumi <malik(dot)a(dot)rumi(at)gmail(dot)com> wrote:

> @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
>>>
>>