Input of anonymous composite types is not implemented - Postgres

Question

I have a table with an XML column. The XML can contain an optional link element.

<Link ReasonCode="MultiLabel">
  <UUID>1d177dee-1da4-4451-b175-396666afc370</UUID>
</Link>

I want to fetch all records from the table that do not have link element. From those records that have the link element, I want only the DISTINCT records fetched. If two records have the same link element, skip the second occurrence. The query that I tried,

SELECT DISTINCT cast((xpath('/Data/Link', xmldata)) AS TEXT) 
FROM tblData AS link 
WHERE link != '{}'

ERROR: input of anonymous composite types is not implemented

1. What is it that I am doing wrong here that causes the above mentioned error? The query works fine without the WHERE clause.
2. In addition to this I want to use an order by clause before the DISTINCT method starts processing the results. Again, I messed up with the syntax of using order by and distinct together, and I got an error.

How do I achieve ordering of the results based on the value of a column in the table, for this query?

Answer 1

In the expression link != '{}' the alias link refers to the table, not a single column (because of tblData AS link).

Postgres allows to refer to the complete row of a table by using the table name (or table alias). The error is telling you that you can't compare a complete row with a single array value.

You need something like this:

select distinct cast(xpath('/Data/Link', xmldata) AS TEXT) as linkdata
from data 
where cast(xpath('/Data/Link', xmldata) AS TEXT) <> '{}';

Unfortunately it's not possible to use a column alias in the where clause. If you want to avoid repeating the expression, use a derived table:

select distinct linkdata 
from (
  select cast(xpath('/Data/Link', xmldata) AS TEXT) as linkdata
  from data 
) t 
where linkdata <> '{}';

But as you are only expecting a single link anyway, the following might better:

select distinct linkdata 
from (
  select (xpath('/Data/Link', xmldata))[1] as linkdata
  from data 
) t 
where linkdata is not null;

xpath() returns an array, the expression (xpath(..))[1] gets the first element of that array, if there is no such element, it returns null. Maybe you event want xpath('/Data/Link/UUID/text()') to only get the actual UUID, rather than the complete XML node - but that's not clear from the example you provided.