Re: Trigger is not working for Inserts from the application

On 09/10/2016 02:09 PM, Kiran wrote:
> Hi Adrian,
>
> The JSON value is inserted into a column in the database which I can see.
> But the trigger which has to convert this JSON value in not tsvector
> column is not updating that column.

As Tom explained, in your trigger function you have:

to_tsvector('swedish',coalesce(New.body->>'qtext','')::text);

In the body JSON you are passing in:

$4 = '{"name": "Do you like Pizza ?", "type": "cat", "store":
[{"labeltext": "Yes", "labelvalue": 1}, {"labeltext": "No",
"labelvalue": 2}, {"labeltext": "Other", "labelvalue": 3}, {"labeltext":
"Don''t know", "labelvalue": 4}], "target": {"place": "Sweden"},
"askfreq": "once", "whydesc": "Because I like it :)"}'

there is not 'qtext', so New.body->>'qtext' is getting you NULL which
the COALESCE is turning into '' which is making weighted_tsv look empty.

>
> regards
>
> On Sat, Sep 10, 2016 at 10:27 PM, Tom Lane <tgl@sss.pgh.pa.us
> <mailto:tgl@sss.pgh.pa.us>> wrote:
>
>     Kiran <bangalore.kiran@gmail.com <mailto:bangalore.kiran@gmail.com>>
>     writes:
>     > LOG:  execute <unnamed>: INSERT INTO "myschema"."cf_question"
>     > ("cf_question_type_id", "cf_question_category_id", "lang", "body") VALUES
>     > ($1, $2, $3, $4) RETURNING *
>     > DETAIL:  parameters: $1 = '1', $2 = '9', $3 = 'swedish', $4 = '{"name": "Do
>     > you like Pizza ?", "type": "cat", "store": [{"labeltext": "Yes",
>     > "labelvalue": 1}, {"labeltext": "No", "labelvalue": 2}, {"labeltext":
>     > "Other", "labelvalue": 3}, {"labeltext": "Don''t know", "labelvalue": 4}],
>     > "target": {"place": "Sweden"}, "askfreq": "once", "whydesc": "Because I
>     > like it :)"}'
>
>     Well, the obvious comment on that is "that JSON value hasn't got any
>     qtext
>     field".  So the ->> operator is returning null, the coalesce() is
>     replacing that with an empty string, and you end up with an empty
>     tsvector column.
>
>                             regards, tom lane
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com