Informix Guide to SQL: Syntax Segments

Use the Database Object Name segment to specify the name of a constraint, index, trigger, table, synonym, user-defined routine (UDR), or view. Use this segment whenever you see a reference to database object name.

Usage

If you are creating or renaming a database object, the name that you specify must be unique in relation to other database objects of the same type in the database. For example, a new constraint name must be unique among constraint names that exist in the database.

A new name for a table, synonym, or view, must be unique among all the tables, synonyms, views, and temporary tables that already exist in the database.

In Dynamic Server, the uniqueness requirement does not apply to UDR names. For more information, see Routine Overloading and Naming UDRs with a Routine Signature.

In an ANSI-compliant database, the ownername.object combination must be unique in a database.

A database object name must include the owner name for a database object that you do not own. For example, if you specify a table that you do not own, you must specify the owner of the table also. The owner of all the system catalog tables is informix.

If you are using a nondefault locale, you can use characters from the code set of your locale in database object names. For more information, see the Informix Guide to GLS Functionality.

To specify an object on a remote database server, you must use a fully qualified identifier. A fully qualified identifier includes the names of the database, database server, and owner in addition to the database object name.

The following example shows a fully qualified table name:

empinfo@personnel:markg.emp_names 

In this example, the name of the database is empinfo. The name of the database server is personnel. The name of the owner of the table is markg. The name of the table is emp_names.

Restrictions with UDRs on Remote Database Servers

If a UDR exists on a remote database server, you must specify a fully qualified identifier for the UDR. In addition, the UDR must meet the following requirements:

• All of the arguments passed to the UDR are of built-in data types.
• All of the parameters that the UDR accepts are of built-in data types.
• Any values that a user-defined function returns are of built-in data types.

In other words, you cannot specify a remote UDR if any of its parameters or return values are opaque, distinct, collection, or row types.

Because of routine overloading, the name of a UDR (that is, a user-defined function or a user-defined procedure) does not have to be unique to the database. You can define more than one UDR with the same name as long as the routine signature for each UDR is different.

UDRs are uniquely identified by their signature. The signature of a UDR includes the following items:

• The type (function or procedure)
• The name
• The quantity, data type, and order of the parameters
• In an ANSI database, the owner name.

For any given UDR, at least one item in the signature must be unique among all the UDRs stored in a name space or database.

When you are specifying the name of an existing UDR, if the name you specify does not uniquely identify the UDR, you must also specify the parameter data types after the UDR name. You must specify the parameter data types in the same order that they were specified when the UDR was created. The database server then uses routine resolution to identify the instance of the UDR to alter, drop, or execute.

As an alternative you can specify the specific name for the UDR if one was given to it when it was created.

For more information about routine resolution, see Extending Informix Dynamic Server 2000.