Search Postgresql Archives

Re: Triggers and Full Text Search *

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux