Thread: Fixing set-returning functions are not allowed in UPDATE after upgrade to Postgres 12

Hi!

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 ?


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
In Postgres 9.1 it worked.

Andrus. 

On Thu, 2020-04-02 at 17:48 +0300, Andrus wrote:
> 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 ?

Simply replace

   SET col = unnest(array_value)

with

   SET col = array_value[1]

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

Hi!

>Simply replace
>   SET col = unnest(array_value)
>with
>
>   SET col = array_value[1]

I tried

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

How to fix ?

Posted also in 

https://stackoverflow.com/questions/60993975/fixing-set-returning-functions-are-not-allowed-in-update

Andrus.