Re: Triggers and Full Text Search *

@Ericson
I have the script (statements?) by which I created the triggers, but since
you asked I do not see them in pga4, from which I manage my postgres. I
don't know if this is significant or not. Also, this was originally done
quite a while ago, so my memory may be fuzzy. From the text of the
statement "runSQL..." I think I ran this in the terminal. So this is the
closest thing I can find to your request. The text that I previously posted
can be found in 'triggers functions' under this schema in pga4, but not
these statements. Also, further up the pga4 tree, "event triggers" is
blank. I mention these things because I am not sure of their importance.
Thanks.

triggers.py

# Trigger on insert or update of ktab.Entry

migrations.RunSQL('''CREATE OR REPLACE FUNCTION
entry_search_vector_trigger() RETURNS trigger AS $$
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(tags.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_tags AS tag ON tag.id = entry_tags.tag_id
WHERE entry.id = NEW.id
GROUP BY entry.id, category;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER search_vector_update BEFORE INSERT OR UPDATE ON ktab_entry
FOR EACH ROW EXECUTE PROCEDURE entry_search_vector_trigger();''')

# Trigger after ktab.Author is updated
'''
Since I don't have author, and besides, his author was a separate table -
SKIP
CREATE OR REPLACE FUNCTION author_search_vector_trigger() RETURNS trigger
AS $$
BEGIN
UPDATE ktab_entry SET id = id WHERE author_id = NEW.id;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER search_vector_update AFTER INSERT OR UPDATE ON ktab_author
FOR EACH ROW EXECUTE PROCEDURE author_search_vector_trigger();
'''

# Trigger after ktab.Entry.tags are added, deleted from a entry
migrations.RunSQL('''CREATE OR REPLACE FUNCTION
tags_search_vector_trigger() RETURNS trigger AS $$
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;
$$ LANGUAGE plpgsql;
CREATE TRIGGER search_vector_update AFTER INSERT OR UPDATE OR DELETE ON
ktab_entry_tags
FOR EACH ROW EXECUTE PROCEDURE tags_search_vector_trigger();
''')

# Trigger after ktab.Tag is updated
migrations.RunSQL('''CREATE OR REPLACE FUNCTION tag_search_vector_trigger()
RETURNS trigger AS $$
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;
$$ LANGUAGE plpgsql;
CREATE TRIGGER search_vector_update AFTER UPDATE ON ktab_tag
FOR EACH ROW EXECUTE PROCEDURE tag_search_vector_trigger();

*“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:25 PM Ericson Smith <esconsult1(at)gmail(dot)com> wrote:

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