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

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.

On Thu, Apr 2, 2020 at 11:38 PM Andrus <kobruleht2@hot.ee> wrote:

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

Parens around the function call expression are required per the syntax
documentation for array subscript access:

https://www.postgresql.org/docs/current/sql-expressions.html#SQL-EXPRESSIONS-SUBSCRIPTS

update temprid set
ContactFirstName =(xpath(

'/E-Document/Document/DocumentParties/BuyerParty/ContactData/ContactFirstName/text()',x)
)[1]::text

David J.