An Example of Casting Between Named Row Types

Suppose you create the named row types and table shown in the next example. Although the named row types are structurally equivalent, writer_t and editor_t are unique data types.

CREATE ROW TYPE writer_t (name VARCHAR(30), depart CHAR(3));
CREATE ROW TYPE editor_t (name VARCHAR(30), depart CHAR(3));

CREATE TABLE projects
(
   book_title  VARCHAR(20),
   writer      writer_t,
   editor      editor_t
);

To handle conversions between two named row types, you must first create a user-defined cast. The following example creates a casting function and registers it as a cast to handle conversions from type writer_t to editor_t:

CREATE FUNCTION cast_rt (w writer_t)
   RETURNS editor_t
   RETURN (ROW(w.name, w.depart)::editor_t); 
END FUNCTION;

CREATE CAST (writer_t as editor_t WITH cast_rt);

Once you create and register the cast, you can explicitly cast values of type writer_t to editor_t. The following query uses an explicit cast in the WHERE clause to convert values of type writer_t to editor_t:

SELECT book_title FROM projects
   WHERE CAST(writer AS editor_t) = editor;

If you prefer, you can use the :: cast operator to perform the same cast, as the following example shows:

SELECT book_title FROM projects
   WHERE writer::editor_t = editor;