After upgrading to Postgres 12 statement
update temprid set ContactFirstName =unnest(xpath( '/E-Document/Document/DocumentParties/BuyerParty/ContactData/ContactFirstName/text()',x))::text, yhik =unnest(xpath( '/E-Document/Document/DocumentItem/ItemEntry/BaseUnit/text()',x))::text from t
throws error
set-returning functions are not allowed in UPDATE
How to fix this ? In Postgres 9.1 it worked. I read similar answers here but they recomment to totally overwrite statement. Maybe there is simply change which makes this statement work ?
Result should by any value of xpath expression in case if xpath returns multiple values
Update
I tried according to answer
update temprid set ContactFirstName =xpath( '/E-Document/Document/DocumentParties/BuyerParty/ContactData/ContactFirstName/text()',x)[1]::text
But got error
ERROR: syntax error at or near "[" LINE 3: .../BuyerParty/ContactData/ContactFirstName/text()',x)[1]::text
Advertisement
Answer
Simply replace
SET col = unnest(array_value)
with
SET col = array_value[1]
In your case, you have to use an extra pair of parentheses, because the array_value
is an expression:
SET ContactFirstName = (xpath( '/E-Document/Document/DocumentParties/BuyerParty/ContactData/ContactFirstName/text()', x) )[1]