PostgreSQL : ERROR: set-returning functions are not allowed in WHERE

Question

I'm new to PostgreSQL, and I have the following code

select unnest(xpath(
    '//ns2:ProcedureCategory/text()',messagebody::xml,
    array[array['ns2','urn:wco:datamodel:WCO:DEC-DMS:2']]
))::text
from sw_customs_message scm 
where unnest(xpath(
    '//ns2:ProcedureCategory/text()',messagebody::xml,  
    array[array['ns2','urn:wco:datamodel:WCO:DEC-DMS:2']]
))::text = 'H7'

and I get the error message

SQL Error [0A000]: ERROR: set-returning functions are not allowed in WHERE Position: 172

user330315 · Accepted Answer · 2022-06-08 19:53:10Z

2

Set returning functions should be used in the FROM clause. Then you can also reference the result columns in the WHERE clause:

select u.val::text
from sw_customs_message scm 
   cross join unnest(xpath('//ns2:ProcedureCategory/text()', 
                     scm.messagebody::xml,  
                     array[array['ns2','urn:wco:datamodel:WCO:DEC-DMS:2']])) as u(val)
where u.val::text = 'H7'

Note that typically xmltable() is easier and more flexible to use if you want to turn an XML value into rows and columns. And if you are storing XML in a column, the column should be defined as xml not something else.

answered Jun 8, 2022 at 19:53

user330315

Sign up to request clarification or add additional context in comments.

2 Comments

Luca Ruggeri Over a year ago

on the same XML i would like to extract also another value ns2:FunctionalReferenceID . how i can do it ?

user330315 Over a year ago

@LucaRuggeri: as I said: xmltable() is typically much easier to use. But that is a totally different question now (and not really answerable without proper sample data)

Collectives™ on Stack Overflow

PostgreSQL : ERROR: set-returning functions are not allowed in WHERE

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related