Fixing set-returning functions are not allowed in UPDATE

user_742402 0
Viewed 630

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
xml-parsing xpath postgresql sql
1 Answers

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]
user_64643080
Related