Dear Tom, Adrian,
Excellent catch!
Thank you very very much to both. It is resolved now. I can sleep now.
Good night from Sweden.
regards
Kiran
On Sat, Sep 10, 2016 at 11:18 PM, Adrian Klaver <adrian.kla...@aklaver.com>
wrote:
> 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 <t...@sss.pgh.pa.us
>> <mailto:t...@sss.pgh.pa.us>> wrote:
>>
>> Kiran <bangalore.ki...@gmail.com <mailto:bangalore.ki...@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.kla...@aklaver.com
>
