Input of anonymous composite types is not implemented - PostgreSQL

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?

Runkel

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.

Footlambert

Well that has cleared up the mistake I did with aliasing. However, I want to fetch all records that do not have the link element at all. I want to apply distinct only on those records that have the link element, which is optional. – Runkel

I will go with the last option that you have suggested. But is there a difference in performance in using a derived table and repeating the cast expression? Or is it only to avoid repeating the expression? – Runkel

Answer 2

I got the error below:

ERROR: input of anonymous composite types is not implemented

When I tried to create the array of rows with ::RECORD[] or ::RECORD as shown below:

SELECT ARRAY['(John,Smith)','(David,Miller)']::RECORD[];

Or:

SELECT ARRAY['(John,Smith)'::RECORD,'(David,Miller)'::RECORD];

So, I used ROW() instead of ::RECORD[] or ::RECORD as shown below, then I could create the array of rows without error. *The type of the array below is RECORD[]:

SELECT ARRAY[ROW('John','Smith'),ROW('David','Miller')];

Interscholastic