Casting Between Named and Unnamed Row Types

To compare values of a named row type with values of an unnamed row type, you can use an explicit cast. Suppose that you create the following named row type and tables:

CREATE ROW TYPE info_t (x CHAR(1), y CHAR(20))
CREATE TABLE customer (cust_info info_t)
CREATE TABLE retailer (ret_info ROW (a CHAR(1), b CHAR(20)))
INSERT INTO customer2 VALUES(ROW('t','philips')::info_t2)

The following INSERT statements show how to create row-type values for the customer and retailer tables:

INSERT INTO customer VALUES(ROW('t','philips')::info_t)
INSERT INTO retailer VALUES(ROW('f','johns'))

To compare or substitute data from the customer table with data from retailer table, you must use an explicit cast to convert a value of one row type to the other row type. In the following query, the ret_info column (an unnamed row type) is explicitly cast to info_t (a named row type):

SELECT cust_info
FROM customer, retailer
WHERE cust_info = ret_info::info_t

In general, to perform a conversion between a named row type and an unnamed row type, you must explicitly cast one row type to the other row type. You can perform an explicit cast in either direction: you can cast the named row type to an unnamed row type or cast the unnamed row type to a named row type. The following statement returns the same results as the previous example. However, the named row type in this example is explicitly cast to the unnamed row type:

SELECT cust_info
FROM customer, retailer
WHERE cust_info::ROW(a CHAR(1), b CHAR(20)) = ret_info