After upgrading to Postgres 12 statement
x
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]