Syntax

(1 of 2)

Element	Purpose	Restrictions	Syntax
source data type	The data type to be converted	The type must exist in the database at the time the cast is registered. Either the source data type or the target data type, but not both, can be a built-in type. Neither type can be a distinct type of the other. The type cannot be a collection data type.	Data Type, p. 1-859
target data type	The data type that results from the conversion	The type must exist in the database at the time the cast is registered. Either the source data type or the target data type, but not both, can be a built-in type. Neither type can be a distinct type of the other. The type cannot be a collection data type.	Data Type, p. 1-859
function name	The name of the function that you register to implement the cast	See "WITH Clause".	Function Name, p. 1-963

• To compare two values in the WHERE clause of a SELECT, UPDATE, or DELETE statement
• To pass values as arguments to a user-defined routines
• To return values from user-defined routines

The CREATE CAST statement registers a cast in the syscasts system catalog table. For more information on syscasts, see the chapter on system catalog tables in the Informix Guide to SQL: Syntax.

Source and Target Data Types

• Either the source data type or the target data type, but not both, can be a built-in type.
• Neither the source data type nor the target data type can be a distinct type of the other.
• Neither the source data type nor the target data type can be a collection data type.

• Use the CREATE EXPLICIT CAST statement to define an explicit cast.
• Use the CREATE IMPLICIT CAST statement to define an implicit cast.

The following CREATE CAST statement defines an explicit cast from the rate_of_return opaque data type to the percent distinct data type:

CREATE EXPLICIT CAST (rate_of_return AS percent) 

	WITH rate_to_prcnt

SELECT bond_rate FROM bond

WHERE bond_rate::percent > 15

An implicit cast is a cast that the database server can invoke automatically when it encounters data types that cannot be compared with system-defined casts. This type of cast enables the database server to handle automatically conversions between other data types.

To define an implicit cast, specify the IMPLICIT keyword in the CREATE CAST statement. For example, the following CREATE CAST statement specifies that the database server should automatically use the prcnt_to_char() function when it needs to convert from the CHAR data type to a distinct data type, percent:

CREATE IMPLICIT CAST (CHAR AS percent) WITH prcnt_to_char

CREATE IMPLICIT CAST (percent AS CHAR) WITH char_to_prcnt

SELECT commission FROM sales_rep

WHERE commission > "25%"

When a system-defined cast does not exist for conversion between data types, you can create user-defined casts to make the necessary conversion. Universal Server supports the following types of casts:

WITH Clause

• Both data types have the same length and alignment
• Both data types are passed by reference or both are passed by value.

References

See the Data Types segment in this manual and Chapter 2 in the Informix Guide to SQL: Syntax for more information about data types, casting, and conversion.

In the Informix Guide to SQL: Tutorial, see Chapter 13 for examples that show how to create and use casts.

CREATE DATABASE

Syntax

Element	Purpose	Restrictions	Syntax
dbspace	The name of the dbspace where you want to store the data for this database; default is the root dbspace	The dbspace must already exist.	Identifier, p.1-966
pathname	The full pathname, including the file name, for the log file	You cannot specify an existing file.	The pathname and filename must conform to the conventions of your operating system.

The database name that you use must be unique within the database server environment in which you are working. The database server creates the system catalog tables that contain the data dictionary, which describes the structure of the database in the dbspace. If you do not specify the dbspace, The database server creates the system catalog tables in the root dbspace.

When you create a database, you alone have access to it. The database remains inaccessible to other users until you, as DBA, grant database privileges. For information on granting database privileges, see the GRANT statement on page 1-461.

The following statement creates the vehicles database in the root dbspace:

CREATE  DATABASE  vehicles 

CREATE  DATABASE  vehicles IN research 

ESQL

In SQL APIs, the CREATE DATABASE statement cannot appear in a multistatement PREPARE operation.

ANSI-Compliant Databases

• All statements are automatically contained in transactions. All databases on the database server use unbuffered logging.
• Owner-naming is enforced. You must use the owner name when you refer to each table, view, synonym, index, or constraint unless you are the owner.
• For databases on the database server, the default isolation level available is Repeatable Read.
• Default privileges on objects differ from those in databases that are not ANSI compliant. Users do not receive the PUBLIC privilege to tables and synonyms by default.

Other slight differences exist between databases that are ANSI compliant and those that are not. These differences are noted as appropriate with the related SQL statement.

Logging on INFORMIX-Universal Server

If you do not specify the WITH LOG clause, you cannot use transactions or the statements that are associated with databases that have logging (BEGIN WORK, COMMIT WORK, ROLLBACK WORK, SET LOG, and SET ISOLATION).

Designating Buffered Logging

CREATE  DATABASE  vehicles  WITH  BUFFERED  LOG 

ANSI

An ANSI-compliant database does not use buffered logging.

Designating an ANSI-Compliant INFORMIX-Universal Server Database

CREATE  DATABASE  employees  WITH  LOG  MODE  ANSI 

For additional information about -ansi and DBANSIWARN, see Chapter 3 in the Informix Guide to SQL: Syntax.

References

In the Informix Guide to SQL: Tutorial, see the discussion of creating a database in Chapter 9.

CREATE DISTINCT TYPE

Syntax

Element	Purpose	Restrictions	Syntax
distinct type	The name of the new data type	In an ANSI-compliant database, the combination of the owner and data type must be unique within the database. In a database that is not ANSI compliant, the name of the data type must be unique within the database.	Data Type, p. 1-859
source type	The name of an existing data type on which the new type is based	The type must be either a built-in type or a type created with the CREATE DISTINCT TYPE, CREATE OPAQUE TYPE, or CREATE ROW TYPE statement.	Data Type, p. 1-859

Important: You cannot create a distinct type on the SERIAL or SERIAL8 data type.

To create a distinct type from an opaque type, a named row type, or another distinct type, you must be the owner of the type or have the Usage privilege on the type.

Once a distinct type is defined, only the type owner and the DBA can use it. The owner of the type can grant other users the Usage privilege on the type.

A distinct type has the same storage structure as its source type.The following statement creates the distinct type birthday, based on the built-in data type, DATE:

CREATE DISTINCT TYPE birthday AS DATE

Support Functions and Casts

• A cast from the distinct type to its source type
• A cast from the source type to the distinct type

You can create an implicit cast between a distinct type and its source type. However, to create an implicit cast, you must first drop the default explicit cast between the distinct type and its source type.

All support functions and casts that are defined on the source type can be used on the distinct type. However, casts and functions that are defined on the distinct type are not available to the source type.

Manipulating Distinct Types

For example, suppose you create a distinct type, dist_type, that is based on the NUMERIC data type. You then create a table with two columns, one of type dist_type and one of type NUMERIC.

CREATE DISTINCT TYPE dist_type AS NUMERIC;
CREATE TABLE t(col1 dist_type, col2 NUMERIC);

INSERT INTO tab (col1) VALUES (3.5::dist_type);


SELECT col1, col2 

	FROM t WHERE (col1::NUMERIC) > col2;


SELECT col1, col2, (col1 + col2::dist_type) sum_col 

	FROM tab;

See the CREATE OPAQUE TYPE and CREATE ROW TYPE statements in this manual for information about how to create opaque types and row types.

See the CREATE FUNCTION statement in this manual for information about registering support functions for a type. See the CREATE CAST statement in this manual for information about registering these functions as casts.

For information about how to remove opaque types and row types from a database, see the DROP TYPE and DROP ROW TYPE statements in this manual.

For information about how to create a table that references a data type, see the CREATE TABLE statement in this manual.

For information about built-in data types, user-defined types, and named row types, see the Data Types segment in this manual.

CREATE FUNCTION

Syntax

Usage

• Stored Procedure Language (SPL functions)

• One of the external languages (such as C) that INFORMIX-Universal Server supports (External functions)

The entire length of a CREATE FUNCTION statement must be less than 64 kilobytes. This length is the literal length of the statement, including blank space and tabs.

Routines, Functions, and Procedures

Legacy Procedures

SPL

SPL Functions

Use one CREATE FUNCTION statement, with SQL and SPL statements embedded between CREATE FUNCTION and END FUNCTION, to write and register an SPL function. Unlike external functions, you do not need to write the function and register it in separate steps.

SPL functions are parsed, optimized (as far as possible), and stored in the system catalog tables in executable format. The body of an SPL function is stored in the sysprocbody system catalog table. Other information about the function is stored in other system catalog tables, including sysprocedures, sysprocplan, and sysprocauth. For more information about these system catalog tables, see Chapter 1, "System Catalog," in the Informix Guide to SQL: Syntax.

You must use the END FUNCTION keywords with an SPL function.

Place a semicolon after the Return clause or the Modifier clause, whichever comes last. Place another semicolon at the end of the entire statement, after the END FUNCTION, DOCUMENT, or WITH LISTING IN clause.

Examples

CREATE FUNCTION update_by_pct ( pct INT, pid CHAR(10))
	RETURNING INT;

	DEFINE n INT;

	UPDATE inventory SET price = price + price * (pct/100)
		WHERE part_id = pid;
	LET n = price;
	RETURN price;

END FUNCTION
	DOCUMENT "USAGE: Update a price by a percentage",
			"Enter an integer percentage from 1 - 100",
			"and a part id number"
	WITH LISTING IN '/tmp/warn_file';

EXT

External Functions

1. Write the function in an external language, such as C, that INFORMIX-Universal Server supports.

2. Compile the function and store the compiled code in a shared library.

3. Register the function in the database server with the CREATE FUNCTION statement.

The database server does not store the body of an external function directly in the database, as it does for SPL functions. Instead, the database server stores only a pathname to the compiled version of the function. You specify this pathname in the External Routine Reference clause.

The database server does store information about an external function in several system catalog tables, including sysprocbody and sysprocauth. For more information on these system catalog tables, see Chapter 1, "System Catalog," in the Informix Guide to SQL: Syntax.

With external functions, the END FUNCTION keywords are optional.

Example

CREATE FUNCTION equal ( arg1 opaquetype1, arg2 opaquetype1)

RETURNING BOOLEAN;

EXTERNAL NAME 
"/usr/lib/opaquetype1/lib/libbtype1.so(opaquetype1_equal)"

LANGUAGE C

END FUNCTION;

DBA Option

If you create a function with the DBA option, it is known as a DBA-privileged function. You need the DBA privilege to create or execute a DBA-privileged function.

If you do not use the DBA option, the function is known as an owner-privileged function. If the function is owner privileged, and if the database is ANSI compliant, anyone can execute the function.

If you create an owner-privileged routine in a database that is not ANSI compliant, the NODEFDAC environment variable prevents privileges on that routine from being granted to PUBLIC. See the Informix Guide to SQL: Syntax for further information on the NODEFDAC environment variable.

Function Name

The process of overloading routines and the routine resolution rules are described briefly in "Routine Resolution".

The syntax of the Function Name segment is described in "Function Name".

Parameter List

EXT

To define the parameters for an external routine, you can specify a name and you must specify a data type for each parameter. For more information on the syntax of the parameter list, see "Routine Parameter List".

With both SPL functions and external functions, you can specify an OUT parameter, so that the function can be used with a Statement Local Variable in SQL statements. The OUT parameter is described in more detail in "Routine Parameter List".

Return Clause

The syntax of the Return clause is described in "Return Clause".

Specific Name

The syntax of the specific name is described in "Specific Name".

Function Modifier

EXT

In the CREATE FUNCTION statement, you can specify any of a list of function modifiers with a WITH clause. For more information on the function modifiers, see "Routine Modifier".

Statement Block

External Routine Reference

DOCUMENT Clause

SPL

To find the description of the SPL procedure update_by_pct, shown in "SPL Functions", enter a query such as the following:

SELECT data FROM sysprocbody b, sysprocedures p

WHERE b.procid = p.procid 
		--join between the two catalog tables

	AND p.procname = 'raise_prices' 
		-- look for procedure named raise_prices

	AND b.datakey  = 'D'	-- want user document

ORDER BY b.seqno;

USAGE: Update a price by a percentage
Enter an integer percentage from 1 - 100
and a part id number

EXT

You can use a DOCUMENT clause at the end of the CREATE FUNCTION statement, whether or not you use END FUNCTION.

WITH LISTING IN Clause

If you specify a filename but not a directory in the WITH LISTING IN clause, INFORMIX-Universal Server uses the home directory on the database server as the default directory. If you do not have a home directory on the server, the file is created in the root directory.

If you do not use the WITH LISTING IN option, the compiler does not generate a list of warnings.

Privileges Necessary for Using CREATE FUNCTION

The owner of a privilege grants the Execution privilege for that function to other users. If a function has a commutator function, any user who executes the function must have Execute privilege on both the function and its commutator. If a function has a negator function, any user who executes the function must have Execute privilege on both the function and its negator.

Routine Resolution

• You create a routine with the same name as a built-in function (such as equal()) to process a new user-defined data type.
• You create type hierarchies, in which subtypes inherit data representation and functions from supertypes.
• You create distinct types, which are data types that have the same internal storage representation as an existing data type, but have different names and cannot be compared to the source type without casting. Distinct types inherit functions from their source types.

PREPARE Statement

References

In the Informix Guide to SQL: Tutorial, see the discussion of creating and using SPL routines in Chapter 14.

In the Extending INFORMIX-Universal Server: User-Defined Routines manual, see the discussion of how to create and use external functions.

CREATE FUNCTION FROM

Syntax

Element	Purpose	Restrictions	Syntax
filename	The pathname and filename of the file that contains the full text of a CREATE FUNCTION statement. The default pathname is the current directory.	The specified file must exist.	The pathname and filename must conform to the conventions of your operating system.
variable name	The name of a program variable that holds the value of filename	The file that is specified in the program variable must exist.	The name must conform to language-specific rules for variable names.

Usage

• Create a source file with the CREATE FUNCTION statement.
• Use the CREATE FUNCTION FROM statement to send the contents of this source file to the database server for execution.

CREATE FUNCTION delete_order( p_order_num int )
	RETURNING int, int,;
	DEFINE item_count int;
	SELECT count(*) INTO item_count FROM items
		WHERE order_num = p_order_num;
	DELETE FROM orders
		WHERE order_num = p_order_num;
	RETURN p_order_num, item_count;
END FUNCTION;

EXEC SQL create function from 'del_ord.sql';

Important: The ESQL/C preprocessor does not process the contents of the file that you specify. It just sends the contents to the database server for execution. Therefore, there is no syntactic check that the file that you specify in CREATE FUNCTION FROM actually contains a CREATE FUNCTION statement. However, to improve readability of the code, Informix recommends that you match these two statements. If you are not sure whether the routine is a function or a procedure, use the CREATE ROUTINE FROM statement in the ESQL/C program.

References

In the Informix Guide to SQL: Tutorial, see the discussion of creating and using stored functions in Chapter 14.