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

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

CodePudding user response：

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.