[prev in list] [next in list] [prev in thread] [next in thread]
List: postgresql-general
Subject: Re: Triggers and Full Text Search *
From: Malik Rumi <malik.a.rumi () gmail ! com>
Date: 2020-04-25 18:12:37
Message-ID: CAKd6oBx=nOeM60ZEHeaFupNNEgJRorEpJy7Xh-RkPoLSnrOJ+Q () mail ! gmail ! com
[Download RAW message or body]
[Attachment #2 (multipart/alternative)]
@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@gmail.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.a.rumi@gmail.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@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
>>>>
>>>
[Attachment #5 (text/html)]
<div dir="ltr"><div>@Ericson<br></div><div>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.</div><div><br></div><div>triggers.py</div><div><br></div><div># Trigger on \
insert or update of ktab.Entry<br><br>migrations.RunSQL('''CREATE OR \
REPLACE FUNCTION entry_search_vector_trigger() RETURNS trigger AS $$<br>BEGIN<br> \
SELECT setweight(to_tsvector(NEW.title), 'A') ||<br> \
setweight(to_tsvector(NEW.content), 'B') ||<br> \
setweight(to_tsvector(NEW.category), 'D') ||<br> \
setweight(to_tsvector(COALESCE(string_agg(tags.tag, ', '), '')), \
'C')<br> INTO NEW.search_vector<br> FROM ktab_entry AS entry<br> \
LEFT JOIN ktab_entry_tags AS entry_tags ON entry_tags.entry_id = <a \
href="http://entry.id">entry.id</a><br> LEFT JOIN ktab_tags AS tag ON <a \
href="http://tag.id">tag.id</a> = entry_tags.tag_id<br> WHERE <a \
href="http://entry.id">entry.id</a> = NEW.id<br> GROUP BY <a \
href="http://entry.id">entry.id</a>, category;<br> RETURN NEW;<br>END;<br>$$ \
LANGUAGE plpgsql;<br>CREATE TRIGGER search_vector_update BEFORE INSERT OR UPDATE ON \
ktab_entry<br> FOR EACH ROW EXECUTE PROCEDURE \
entry_search_vector_trigger();''')<br><br># Trigger after ktab.Author is \
updated<br>'''<br>Since I don't have author, and besides, his author \
was a separate table - SKIP<br>CREATE OR REPLACE FUNCTION \
author_search_vector_trigger() RETURNS trigger AS $$<br>BEGIN<br> UPDATE ktab_entry \
SET id = id WHERE author_id = NEW.id;<br> RETURN NEW;<br>END;<br>$$ LANGUAGE \
plpgsql;<br>CREATE TRIGGER search_vector_update AFTER INSERT OR UPDATE ON \
ktab_author<br> FOR EACH ROW EXECUTE PROCEDURE \
author_search_vector_trigger();<br>'''<br><br># Trigger after \
ktab.Entry.tags are added, deleted from a \
entry<br>migrations.RunSQL('''CREATE OR REPLACE FUNCTION \
tags_search_vector_trigger() RETURNS trigger AS $$<br>BEGIN<br> IF (TG_OP = \
'DELETE') THEN<br> UPDATE ktab_entry SET id = id WHERE id = \
OLD.entry_id;<br> RETURN OLD;<br> ELSE<br> UPDATE ktab_entry SET id = id \
WHERE id = NEW.entry_id;<br> RETURN NEW;<br> END IF;<br>END;<br>$$ LANGUAGE \
plpgsql;<br>CREATE TRIGGER search_vector_update AFTER INSERT OR UPDATE OR DELETE ON \
ktab_entry_tags<br> FOR EACH ROW EXECUTE PROCEDURE \
tags_search_vector_trigger();<br>''')<br><br># Trigger after ktab.Tag is \
updated<br>migrations.RunSQL('''CREATE OR REPLACE FUNCTION \
tag_search_vector_trigger() RETURNS trigger AS $$<br>BEGIN<br> UPDATE ktab_entry \
SET id = id WHERE id IN (<br> SELECT entry_id FROM ktab_entry_tags WHERE tag_id \
= NEW.id<br> );<br> RETURN NEW;<br>END;<br>$$ LANGUAGE plpgsql;<br>CREATE TRIGGER \
search_vector_update AFTER UPDATE ON ktab_tag<br> FOR EACH ROW EXECUTE PROCEDURE \
tag_search_vector_trigger();</div><div><br></div><br clear="all"><div><div dir="ltr" \
class="gmail_signature" data-smartmail="gmail_signature"><div dir="ltr"><strong \
style="color:rgb(0,0,0);font-family:Tahoma,sans-serif;font-size:12px;letter-spacing:0.5px"><em>"None \
of you has faith until he loves for his brother or his neighbor what he loves for \
himself."</em></strong><br></div></div></div><br></div><br><div \
class="gmail_quote"><div dir="ltr" class="gmail_attr">On Tue, Apr 21, 2020 at 1:25 PM \
Ericson Smith <<a href="mailto:esconsult1@gmail.com">esconsult1@gmail.com</a>> \
wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px \
0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir="ltr">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).<div><br></div><div>Could you post your \
CREATE TRIGGER statements as well?<br><br></div></div><br><div \
class="gmail_quote"><div dir="ltr" class="gmail_attr">On Wed, Apr 22, 2020 at 1:21 AM \
Malik Rumi <<a href="mailto:malik.a.rumi@gmail.com" \
target="_blank">malik.a.rumi@gmail.com</a>> wrote:<br></div><blockquote \
class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid \
rgb(204,204,204);padding-left:1ex"><div dir="ltr">@Ericson,<div>Forgive me for \
seeming dense, but how does COPY help or hurt \
here?</div><div><br></div><div>@Andreas,</div><div>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.....</div><div><br></div><div>entry_search_vector_trigger<br></div><div> \
BEGIN<br> SELECT setweight(to_tsvector(NEW.title), 'A') \
||<br> setweight(to_tsvector(NEW.content), \
'B') ||<br> \
setweight(to_tsvector(NEW.category), 'D') ||<br> \
setweight(to_tsvector(COALESCE(string_agg(tag.tag, ', '), '')), \
'C')<br> INTO NEW.search_vector<br> \
FROM ktab_entry AS entry<br> LEFT JOIN ktab_entry_tags AS \
entry_tags ON entry_tags.entry_id = <a href="http://entry.id" \
target="_blank">entry.id</a><br> LEFT JOIN ktab_tag AS tag ON \
<a href="http://tag.id" target="_blank">tag.id</a> = entry_tags.tag_id<br> \
WHERE <a href="http://entry.id" target="_blank">entry.id</a> = NEW.id<br> \
GROUP BY <a href="http://entry.id" target="_blank">entry.id</a>, category;<br> \
RETURN NEW;<br> END;<br> \
<br></div><div>tag_search_vector_trigger<br></div><div> BEGIN<br> \
UPDATE ktab_entry SET id = id WHERE id IN (<br> SELECT \
entry_id FROM ktab_entry_tags WHERE tag_id = NEW.id<br> );<br> \
RETURN NEW;<br> END;<br> \
<br></div><div>tags_search_vector_trigger<br></div><div> BEGIN<br> \
IF (TG_OP = 'DELETE') THEN<br> UPDATE ktab_entry SET \
id = id WHERE id = OLD.entry_id;<br> RETURN OLD;<br> \
ELSE<br> UPDATE ktab_entry SET id = id WHERE id = \
NEW.entry_id;<br> RETURN NEW;<br> END \
IF;<br> END;<br> \
<br></div><div>search_vector_update<br></div><div> BEGIN<br> \
SELECT setweight(to_tsvector(NEW.title), 'A') ||<br> \
setweight(to_tsvector(NEW.content), 'B') ||<br> \
setweight(to_tsvector(NEW.category), 'D') ||<br> \
setweight(to_tsvector(COALESCE(string_agg(tag.tag, ', '), '')), \
'C')<br> INTO NEW.search_vector<br> \
FROM ktab_entry AS entry<br> LEFT JOIN ktab_entry_tags AS \
entry_tags ON entry_tags.entry_id = <a href="http://entry.id" \
target="_blank">entry.id</a><br> LEFT JOIN ktab_tag AS tag ON \
<a href="http://tag.id" target="_blank">tag.id</a> = entry_tags.tag_id<br> \
WHERE <a href="http://entry.id" target="_blank">entry.id</a> = NEW.id<br> \
GROUP BY <a href="http://entry.id" target="_blank">entry.id</a>, category;<br> \
RETURN NEW;<br> END;<br> \
<br></div><div>search_vector_update (tags)<br></div><div> \
BEGIN<br> IF (TG_OP = 'DELETE') THEN<br> \
UPDATE ktab_entry SET id = id WHERE id = OLD.entry_id;<br> \
RETURN OLD;<br> ELSE<br> UPDATE ktab_entry \
SET id = id WHERE id = NEW.entry_id;<br> RETURN NEW;<br> \
END IF;<br> END;<br> <br></div><div>Thank \
you!</div><div><br></div><div><br></div><div><br clear="all"><div><div dir="ltr"><div \
dir="ltr"><strong style="color:rgb(0,0,0);font-family:Tahoma,sans-serif;font-size:12px;letter-spacing:0.5px"><em>"None \
of you has faith until he loves for his brother or his neighbor what he loves for \
himself."</em></strong><br></div></div></div><br></div></div><br><div \
class="gmail_quote"><div dir="ltr" class="gmail_attr">On Tue, Apr 21, 2020 at 1:05 PM \
Ericson Smith <<a href="mailto:esconsult1@gmail.com" \
target="_blank">esconsult1@gmail.com</a>> wrote:<br></div><blockquote \
class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid \
rgb(204,204,204);padding-left:1ex"><div dir="ltr">I think COPY bypasses the \
triggers.<div><br clear="all"><div><div dir="ltr"><div dir="ltr"><div><div \
dir="ltr"><div>Best Regards<br>- Ericson Smith</div><div>+1 876-375-9857 \
(whatsapp)</div><div>+1 646-483-3420 \
(sms)</div><div><br></div></div></div></div></div></div><br></div></div><br><div \
class="gmail_quote"><div dir="ltr" class="gmail_attr">On Wed, Apr 22, 2020 at 12:32 \
AM Andreas Joseph Krogh <<a href="mailto:andreas@visena.com" \
target="_blank">andreas@visena.com</a>> wrote:<br></div><blockquote \
class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid \
rgb(204,204,204);padding-left:1ex"><div>På tirsdag 21. april 2020 kl. 19:24:10, \
skrev Malik Rumi <<a href="mailto:malik.a.rumi@gmail.com" \
target="_blank">malik.a.rumi@gmail.com</a>>:</div>
<blockquote style="border-left:1px solid rgb(204,204,204);margin:0pt 0pt 0pt \
0.8ex;padding-left:1ex"> <div dir="ltr">
<div>
<p dir="ltr" style="line-height:1.38;margin-top:0pt;margin-bottom:0pt"><font \
color="#000000" face="Arial"><span \
style="font-size:14.6667px;white-space:pre-wrap">[...]</span></font></p>
<p dir="ltr" style="line-height:1.38;margin-top:0pt;margin-bottom:0pt"><span \
style="font-size:11pt;font-family:Arial;color:rgb(0,0,0);background-color:transparent; \
font-variant-numeric:normal;font-variant-east-asian:normal;vertical-align:baseline;white-space:pre-wrap">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.</span></p> </div>
</div>
</blockquote>
<div> </div>
<div>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:-)</div>
<div> </div>
<div>
<div>--<br>
Andreas Joseph Krogh</div>
</div></blockquote></div>
</blockquote></div>
</blockquote></div>
</blockquote></div>
--000000000000664ed105a4216f0b--
["event triggers 2020-04-25 13-07-57.png" (image/png)]
["triggers 2020-04-25 13-08-46.png" (image/png)]
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic