Re: Triggers and Full Text Search * - Mailing list pgsql-general

@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@gmail.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@visena.com> wrote:

På tirsdag 21. april 2020 kl. 19:24:10, skrev Malik Rumi <malik.a.rumi@gmail.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