Is there a way to get the sql statement about the creation of the row type?

Question

Example:

create row type custom_type string(street VARCHAR(20), city VARCHAR(20); getDDl(custom_type); 

I think the server should store information about row type fields. However, I did not find information about fields or ddl type in the system tables. The documentation says - Field Definition clause defines an ordered list of the data types. But at the moment I couldn't find where this list is stored. Does Informix store information about row type fields anywhere?

Answer 1

The syntax given in the example appears to be incorrect, or at least is not recognized by an Informix server. If it is rewritten as:

CREATE ROW TYPE custom_type(street VARCHAR(20), city VARCHAR(20));

then it will be accepted as valid by Informix.

The command dbschema -d <database> -u all may be used to obtain the definition of the row type. Unlike other options of the dbschema command, only the keyword all may be used to obtain information about all user-defined data types. It is not possible to use the name of a specific row type.

Internally, the information relating to row types is stored in the SYSXTDTYPES and SYSATTRTYPES system catalog tables. An example query to retrieve this information:

SELECT x.name, a.* FROM sysattrtypes a, sysxtdtypes x
WHERE a.extended_id = x.extended_id AND x.name = "custom_type";

This provides the output:

name         custom_type
extended_id  2049
seqno        1
levelno      0
parent_no    0
fieldname
fieldno      0
type         4118
length       42
xtd_type_id  0

name         custom_type
extended_id  2049
seqno        2
levelno      1
parent_no    1
fieldname    street
fieldno      1
type         13
length       20
xtd_type_id  0

name         custom_type
extended_id  2049
seqno        3
levelno      1
parent_no    1
fieldname    city
fieldno      2
type         13
length       20
xtd_type_id  0

Refer to the documentation for the SYSATTRYPES system catalog for details of how to interpret the stored information.