Re: Triggers and Full Text Search *

[Malik Rumi <malik.a.rumi@xxxxxxxxx>]

@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@xxxxxxxxx> 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.a.rumi@xxxxxxxxx> 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@xxxxxxxxx> 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@xxxxxxxxxx> wrote:

På tirsdag 21. april 2020 kl. 19:24:10, skrev Malik Rumi <malik.a.rumi@xxxxxxxxx>:

[...]

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

Attachment: event triggers 2020-04-25 13-07-57.png
Description: PNG image

Attachment: triggers 2020-04-25 13-08-46.png
Description: PNG image