Syntax

Element	Purpose	Restrictions	Syntax
environment variable	A platform-independent location indicator	The environment variable name must begin with a dollar sign and be the first word in the pathname.	Identifier, p. 1-966
pathname	The pathname to the dynamically loadable executable file	An absolute pathname must begin with a forward slash. A relative pathname need not begin with a period. Each directory name must end with a forward slash. The filename at the end of the pathname must end in .so and must refer to an executable file in a shared object library.	Identifier, p. 1-966
symbol	An optional entry point to the dynamically loadable executable	Use a symbol only if the entry point has a different name than the routine you are registering with CREATE FUNCTION or CREATE PROCEDURE. You must enclose a symbol in parentheses.	Identifier, p. 1-966
variable	A platform-independent location indicator that contains the full pathname to the executable object file	You must begin the variable name with a dollar sign.	Identifier, p. 1-966

Usage

You can omit a pathname, and enter just a filename, if you want to refer to an internal function.

A pathname can begin with an environment variable, used as a location indicator. An environment variable begins with a dollar sign, and must be the first element in the pathname.

A pathname can also be absolute or relative. An absolute pathname always begins with a forward slash. A relative pathname need not begin with a period, and is relative from the current directory at the time the CREATE PROCEDURE or CREATE FUNCTION statement is run.

If you use a symbol, it refers to an optional entry point in the executable object file. Use a symbol only if the entry point has a name other than the name of the routine that you are registering with CREATE PROCEDURE or CREATE FUNCTION.

You can also specify the full pathname as a variable that begins with a dollar sign.

You can include spaces or tabs within a Quoted Pathname.

References

For information about how to create and use user-defined routines, see the Extending INFORMIX-Universal Server: User-Defined Routines manual. In the Informix Guide to SQL: Tutorial, see Chapter 14 for information about how to create and use SPL routines.

See the Extending INFORMIX-Universal Server: Data Types manual for information about how to create external routines that define opaque data types and distinct data types.

Quoted String

Syntax

Element	Purpose	Restrictions	Syntax
character	A character that forms part of the quoted string	The character or characters in the quoted string cannot be surrounded by double quotes if the DELIMIDENT environment variable is set. For additional restrictions see "Restrictions on Specifying Characters in Quoted Strings".	Characters are literal values that you enter from the keyboard.

• If you are using the ASCII code set, you can specify any printable ASCII character, including a single quote or double quote. For restrictions that apply to using quotes within quoted strings, see "Using Quotes in Strings".

  GLS

• If you are using a nondefault locale, you can specify non-ASCII characters, including multibyte characters, that the code set of your locale supports. See the discussion of quoted strings in the Guide to GLS Functionality for further information.
• When you set the DELIMIDENT environment variable, you cannot use double quotes to delimit a quoted string. When DELIMIDENT is set, a string enclosed in double quotes is an identifier, not a quoted string. When DELIMIDENT is not set, a string enclosed in double quotes is a quoted string, not an identifier. See "Using Quotes in Strings" for further information.
• You can enter DATETIME and INTERVAL data as quoted strings. See "DATETIME and INTERVAL Values as Strings" for the restrictions that apply to entering DATETIME and INTERVAL data in quoted-string format.
• Quoted strings that are used with the LIKE or MATCHES keyword in a search condition can include wildcard characters that have a special meaning in the search condition. See "LIKE and MATCHES in a Condition" for further information.
• When you insert a value that is a quoted string, you must observe a number of restrictions. See "Inserting Values as Quoted Strings" for further information.

Using Quotes in Strings

"Nancy's puppy jumped the fence"

'Billy told his kitten, "no!"'

"Enter ""y"" to select this row"

DATETIME and INTERVAL Values as Strings

INSERT INTO cust_calls(call_dtime) VALUES ('1993-5-4 10:12:11')



INSERT INTO manufact(lead_time) VALUES ('14')

LIKE and MATCHES in a Condition

Inserting Values as Quoted Strings

• Enclose CHAR, VARCHAR, NCHAR, NVARCHAR, DATE, DATETIME, and INTERVAL values in quotation marks.
• Set DATE values in the mm/dd/yy format.
• You cannot insert strings longer than 256 bytes.
• Numbers with decimal values must contain a decimal point. You cannot use a comma as a decimal indicator.
• You cannot precede MONEY data with a dollar sign ($) or include commas.
• You can include NULL as a placeholder only if the column accepts null values.

In the Guide to GLS Functionality, see the discussion of quoted strings.

Relational Operator

Syntax

Usage

For INTERVAL expressions, greater than means a longer span of time.

For CHAR, VARCHAR, and LVARCHAR expressions, greater than means after in code-set order.

GLS

Locale-based collation order is used for NCHAR and NVARCHAR expressions. So for NCHAR and NVARCHAR expressions, greater than means after in the locale-based collation order. See the Guide to GLS Functionality for further information on locale-based collation order and the NCHAR and NVARCHAR data types.

Using Operator Functions in Place of Relational Operators

Connecting two expressions with a binary operator is equivalent to invoking the function on the expressions. For example, the following two statements both select orders with a shipping charge of $18.00 or more. The >= operator in the first statement implicitly invokes the greaterthanorequal() operator function.

SELECT order_num FROM orders 

	WHERE ship_charge >= 18.00

SELECT order_num FROM orders

	WHERE greaterthanorequal(ship_charge, 18.00)

Collating Order for English Data

• The ASCII characters have code points in the range of 0 to 127.

• The 8-bit characters have code points in the range 128 to 255.

The following table shows the ASCII code set. The Num column shows the ASCII code numbers, and the Char column shows the ASCII character corresponding to each ASCII code number. ASCII characters are sorted according to their ASCII code number. Thus lowercase letters follow uppercase letters, and both follow numerals. In this table, the caret symbol (^) stands for the CTRL key. For example, ^X means CTRL-X.

(1 of 2)

Num	Char	Num	Char	Num	Char
0	^@	43	+	86	V
1	^A	44	,	87	W
2	^B	45	-	88	X
3	^C	46	.	89	Y
4	^D	47	/	90	Z
5	^E	48	0	91	[
6	^F	49	1	92	\
7	^G	50	2	943	]
8	^H	51	3	94	^
9	^I	52	4	95	_
10	^J	53	5	96	\Q
11	^K	54	6	97	a
12	^L	55	7	98	b
13	^M	56	8	99	c
14	^N	57	9	100	d
15	^O	58	:	101	e
16	^P	59	;	102	f
17	^Q	60	<	103	g
18	^R	61	=	104	h
19	^S	62	>	105	i
20	^T	63	?	106	j
21	^U	64	@	107	k
22	^V	65	A	108	l
23	^W	66	B	109	m
24	^X	67	C	110	n
25	^Y	68	D	111	o
26	^Z	69	E	112	p
27	esc	70	F	113	q
28	^\	71	G	114	r
29	^]	72	H	115	s
30	^^	73	I	116	t
31	^_	74	J	117	u
32		75	K	118	v
33	!	76	L	119	w
34	"	77	M	120	x
35	#	78	N	121	y
36	$	79	O	122	z
37	%	80	P	123	{
38	&	81	Q	124	|
39	'	82	R	125	}
40	(	83	S	126	~
41	)	84	T	127	del
42	*	85	U

Support for ASCII Characters in Nondefault Code Sets

GLS

Most code sets in nondefault locales (called nondefault code sets) support the ASCII characters. If you are using a nondefault locale, the database server uses ASCII code-set order for any ASCII data in CHAR and VARCHAR expressions, as long as the nondefault code set supports these ASCII characters.

References

In the Guide to GLS Functionality, see the discussion of relational operator conditions in the SELECT statement.

Return Clause

If you overload functions in your database, the data type of the return value is subject to routine resolution. For more information on routine resolution, see the Extending INFORMIX-Universal Server: User-Defined Routines manual.

SPL

For an SPL function, you can specify more than one data type in the Return clause.

If you write a stored procedure (a legacy SPL function), you can use a Return clause with the CREATE PROCEDURE statement. However, Informix recommends that you create new SPL functions with the CREATE FUNCTION statement. Any routine you create with a Return clause is considered a function.

EXT

For an external function, specify exactly one value in the Return clause. However, an external function can return more than one row of data if it is an iterator function. For more information, see the description of the ITERATOR routine modifier in the Routine Modifier segment.

SQL Data Types (Subset)

An external or SPL function can return values of COLLECTION, SET, MULTISET, LIST, or ROW data type, as the following example shows:

CREATE FUNCTION add_types( a dollar, b yen )
	RETURNING collection1, row1 ... ;

A function can also return a value or values of an opaque or distinct data type that the database defines.

Referencing a Simple Large Object

A function cannot return a CLOB or BLOB, or a pointer to a CLOB or BLOB.

References

In the Informix Guide to SQL: Tutorial, see Chapter 14 for information about how to create and use SPL functions.

See the Extending INFORMIX-Universal Server: User-Defined Routines manual for information about how to create and use external functions.

Routine Modifier

Use a Routine Modifier clause to specify attributes of a user-defined routine behaves. The Routine Modifier clause can be a Function Modifier or a Procedure Modifier.

Function Modifier

Element	Purpose	Restrictions	Syntax
class name	The name of the virtual processor class in which the routine is to run	An external function must run in the CPU VP or in an external VP (EVP) class. If you specify an EVP class, the class must already be defined. You must enclose the class name in single or double quotation marks.	Quoted String, p. 1-1014
stack size	The size of the stack while the routine is running	The stack size must be a positive integer and gives the stack size in bytes. The stack size should be larger than the stack size specified in the STACKSIZE configuration parameter.	Literal Number, p. 1-1001

Procedure Modifier

Element	Purpose	Restrictions	Syntax
class name	The name of the virtual processor class in which the routine is to run	An external procedure must run in the CPU VP or in an external VP (EVP) class. If you specify an EVP class, the class must already be defined. You must enclose the class name in single or double quotation marks.	Quoted String, p. 1-1014
stack size	The size of the stack while the routine is running	The stack size must be a positive integer and gives the stack size in bytes. The stack size should be larger than the stack size specified in the STACKSIZE configuration parameter.	Literal Number, p. 1-1001

Modifier Descriptions

HANDLESNULLS

By default, HANDLESNULLS is not set for external routines. That is, the database server assumes that an external routine does not handle null arguments. If your external routine handles NULL values, specify HANDLESNULLS in the WITH clause of the CREATE FUNCTION or CREATE PROCEDURE statement.

SPL

Do not use HANDLESNULLS with SPL routines. SPL routines handle NULL values by default. You do not need to specify HANDLESNULLS so that an SPL routine handles NULL values.

CLASS

You can run external routines written in C in the CPU virtual processor (CPU VP) class or in an external virtual processor (EVP) class that you name with the CLASS modifier. If you do not specify a VP class, the external routine runs in the CPU VP class by default.

If an external routine is ill-behaved, you must run it in a class other than the CPU VP. A routine is ill-behaved if it does any of the following:

• Runs for a long time without yielding
• Makes an operating system call that can block other calls, for example, READ, WRITE, SELECT, POLL, PUTMSG, BGETMSG, SEMOP, MSGGET, PAUSE, and WAIT
• Modifies the global state of the virtual processor on which it is running by:
  • Modifying global or static data
  • Opening a file descriptor using OPEN, DUP, IOCTL, SOCKET, or similar commands
  • Changing the current working directory
  • Calling the brk(), sbrk(), or malloc() functions
  • Using operating system threads
• Calls one of the following ill-behaved C library functions: stdio(), getpwent(), and gethostbyname().

Use the CLASS modifier in the WITH clause of the CREATE FUNCTION or CREATE PROCEDURE statement to name an external VP class for any routine that might cause the database server to hang, stop running, or behave erratically.

SPL

Do not use CLASS with SPL routines. SPL routines always run in the CPU VP.

VARIANT and NOT VARIANT

By default, user-defined functions are variant. To define a non-variant function, specify NOT VARIANT in the WITH clause of the CREATE FUNCTION statement. If the function is non-variant, the database server may cache the return values of expensive functions or run parallel queries. You can create functional indexes only on non-variant functions. For more information on functional indexes, see the CREATE INDEX statement.

EXT

You can specify VARIANT or NOT VARIANT in the Routine Modifier clause or in the External Routine Reference clause, which is described on page 1-960. If you specify the modifier in both places, you must use the same modifier in both.

STACK

EXT

You can use the STACK modifier with both external procedures and external functions. The STACK modifier enables the database server to run an external routine in a stack that is larger than the stack size that the STACKSIZE configuration parameter specifies. When the external routine executes, the database server increases the stack size of the routine to the number of bytes specified. When the routine completes, the original stack size is restored. Specify a larger stack size to prevent stack overflow.

SPL

You cannot use the STACK modifier with SPL routines.

INTERNAL

By default, an external routine is not internal; that is, an SQL or SPL statement can call the routine. To define an internal function, specify INTERNAL in the WITH clause of the CREATE FUNCTION or CREATE PROCEDURE statement.

SPL

You cannot use the INTERNAL modifier with SPL routines.

ITERATOR

By default, an external function is not an iterator. To define an iterator function, specify ITERATOR in the WITH clause of the CREATE FUNCTION statement.

SPL

You cannot use the ITERATOR modifier with SPL routines.

E/C

Both an iterator function and an SPL function with RETURN WITH RESUME require a cursor to be executed. The cursor allows the client application to retrieve the values one at a time with the FETCH statement.

References

For information about how to create and use external routines, see the Extending INFORMIX-Universal Server: User-Defined Routines manual and the DataBlade API Programmer's Manual. For information about how to create and use SPL routines, see Chapter 14 in the Informix Guide to SQL: Tutorial.

Routine Parameter List

Use the Function Parameter List to define the parameters an external function or SPL function can accept. Use the Procedure Parameter List to define the parameters an external procedure or SPL procedure can accept.

Function Parameter List

Parameter

A Parameter is one item in a Function Parameter List or Procedure Parameter List.

Element	Purpose	Restrictions	Syntax
column name	The name of a column whose data type is assigned to the parameter	The column must exist in the specified table.	Identifier, p. 1-966
parameter name	The name of a parameter the routine can accept	The parameter name is required for SPL routines and optional for external routines in the CREATE FUNCTION and CREATE PROCEDURE statements.	Identifier, p. 1-966
table name	The name of the table that contains column name	The table must exist in the database.	Identifier, p. 1-966
value	The default value that a routine uses if you do not supply a value for the parameter when you call the routine	This value must be a literal. If value is a literal, the value must have the same data type as parameter name. If value is a literal and its type is an opaque type, an input function must be defined on the type.	Literal Number, p. 1-1001

Usage

The data type can be the name of an opaque, distinct, or row type you have defined. The data type can also be COLLECTION, SET, MULTISET, LIST, or ROW with the definition of an unnamed row type. For the complete syntax of all the SQL data types, see "Data Type".

SQL Data Type (Subset)

A parameter can also be of type COLLECTION, which is a generic collection type that can accept any SET, MULTISET, or LIST as an argument.

For more information on defining data types for parameters, see "Data Type".

LIKE Clause

If any of the arguments for the routine are defined using the LIKE clause, you cannot overload the routine and the routine will not be considered in the routine resolution process.

For example, suppose you create the following routine:

CREATE PROCEDURE cost (a LIKE tab.col, b INT)
.
.
.
END PROCEDURE;

REFERENCES Clause

The REFERENCES keyword allows you to use a pointer to a TEXT or BYTE object as a parameter. You cannot use a TEXT or BYTE object directly.

If you use the DEFAULT NULL option in the REFERENCES clause, and you call the routine without a parameter, a null value is used.

Default Value

The following example shows a CREATE FUNCTION statement that specifies a default value for a parameter. This function finds the square of the i parameter. If the function is called without specifying the argument for the i parameter, the database server uses the default value 0 for the i parameter.

CREATE FUNCTION square_w_default

	(i INT DEFAULT 0) {Specifies default value of i}

RETURNING INT; {Specifies return of INT value}

	DEFINE j INT; {Defines procedure variable j}

	LET j = i * i; {Finds square of i and assigns it to j}

	RETURN j; {Returns value of j to calling module}

END FUNCTION;

Warning: When you specify a date value as the default value for a parameter, make sure to specify 4 digits instead of 2 digits for the year. When you specify a 4-digit year, the DBCENTURY environment variable has no effect on how the database server interprets the date value. When you specify a 2-digit year, the DBCENTURY environment variable can affect how the database server interprets the date value, so the routine might not use the default value that you intended. See the "Informix Guide to SQL: Syntax" for more information on the DBCENTURY environment variable.

Once you register a function with an OUT parameter, you can use the function with a Statement Local Variable (SLV) in an SQL statement. You can only mark one parameter as OUT, and it must be the last parameter.

For example, the following declaration of a C language function allows you to return an extra value through the y parameter:

int my_func( int x, int *y );

CREATE FUNCTION my_func( x INT, OUT y INT )
RETURNING INT
EXTERNAL NAME "/usr/lib/local_site.so"
LANGUAGE C
END FUNCTION;

The OUT parameter is not significant in determining the routine signature.

References

For information about how to create and use external routines, see the Extending INFORMIX-Universal Server: User-Defined Routines manual. For information about how to create and use SPL routines, see Chapter 14 in the Informix Guide to SQL: Tutorial.

Specific Name

Syntax

Element	Purpose	Restrictions	Syntax
owner name	The name of the owner of the routine	This name, if used, must be the same owner name used in the Function Name or Procedure Name for this routine. If no owner name is specified in the routine name, then the owner name you use in the Specific Name must be the user id of the person creating the routine. If you omit owner name, the server uses the user id of the person creating the routine.	Identifier, p. 1-966
specific identifier	The unique name of the routine	In a non-ANSI database, the specific identifier must be unique within the database. In other words, two specific names cannot have the same specific identifier even if they have two different owners. In an ANSI database, the specific identifier must be unique for the owner. In other words, the same specific identifier can be used for two routines within the same database if the routines have different owners. The specific identifier can be up to 128 characters long.	Identifier, p. 1-966

Usage

If you give a routine a Specific Name when you create it, you can later alter or drop that routine using the Specific Name only. Otherwise, you need to include the parameter data types with the routine name when you drop the routine, if the routine name alone does not uniquely identify the routine.

You can use a Specific Name in the following SQL statements:

• DROP
• GRANT
• REVOKE
• UPDATE STATISTICS

In an ANSI database, you can use the same specific identifier for two routines within the same database if the routines have different owners.

The Specific Name must be unique within the name space in which it is created:

• For ANSI-compliant databases, the name space is the schema.
• For databases that are not ANSI compliant, the name space is the database.

References

In this manual, see the CREATE FUNCTION, CREATE PROCEDURE, DROP FUNCTION, DROP PROCEDURE, DROP ROUTINE, EXECUTE FUNCTION, and EXECUTE PROCEDURE statements. See also the Function Name and Procedure Name segments.

For information about how to create and use external routines, see the Extending INFORMIX-Universal Server: User-Defined Routines manual. For information about how to create and use SPL routines, see Chapter 14 in the Informix Guide to SQL: Tutorial.

Statement Block

Use a Statement Block, instead of an External Routine Reference, when you write an SPL routine.

Syntax

Usage

If the statement block portion of the statement is empty, no operation takes place when you call the routine. You might use such a routine in the development stage when you want to establish the existence of a routine but have not yet coded it.

Also, you cannot close the current database or select a new database within a routine. And you cannot drop the current stored routine within a stored routine. You can, however, drop another routine.

Subset of SQL Statements Allowed in the Statement Block

You can use any SQL statement in the statement block, except those listed in the following table.

Figure 1-7
SQL Statements That Cannot Be Used in an SPL Routine

ALLOCATE COLLECTION	EXECUTE
ALLOCATE DESCRIPTOR	EXECUTE IMMEDIATE
ALLOCATE ROW	FETCH
CLOSE	FLUSH
CLOSE DATABASE	FREE
CONNECT	GET DESCRIPTOR
CREATE DATABASE	INFO
CREATE FUNCTION	LOAD
CREATE FUNCTION FROM	OPEN
CREATE PROCEDURE	OUTPUT
CREATE PROCEDURE FROM	PREPARE
CREATE ROUTINE	PUT
DEALLOCATE COLLECTION	ROLLFORWARD DATABASE
DEALLOCATE DESCRIPTOR	SET CONNECTION
DEALLOCATE ROW	SET DESCRIPTOR
DECLARE	UNLOAD
DESCRIBE	WHENEVER
DISCONNECT

Subset of SPL Statements Allowed in the Statement Block

You can use any of the following SPL statements in the statement block.

Figure 1-8
SPL Statements that Can Be Used in an SPL Routine

CALL	LET
CONTINUE	RAISE EXCEPTION
EXIT	RETURN
FOR	SYSTEM
FOREACH	TRACE
IF	WHILE

Restrictions on SELECT Statement

You can use a SELECT statement in only two cases:

• You can use the INTO TEMP clause to put the results of the SELECT statement into a temporary table.
• You can use the SELECT... INTO form of the SELECT statement to put the resulting values into SPL variables.

Support for Roles and User Identity

You can use roles with routines you create. You can execute role-related statements (CREATE ROLE, DROP ROLE, and SET ROLE) and SET SESSION AUTHORIZATION statements within a routine. You can also grant privileges to roles with the GRANT statement within a routine. Privileges that a user has acquired through enabling a role or by a SET SESSION AUTHORIZATION statement are not relinquished when a routine is executed.

For further information about roles, see the CREATE ROLE, DROP ROLE, GRANT, REVOKE, and SET ROLE statements in this guide.

Restrictions on a Routine Called in a Data Manipulation Statement

If a routine is called as part of an INSERT, UPDATE, DELETE, or SELECT statement, the called routine cannot execute any statement listed in Figure 1-9. This restriction ensures that the routine cannot make changes that affect the SQL statement that contains the routine call.

Figure 1-9
SQL Statements Not Allowed in an SPL Routine That a Data Manipulation Statement Calls

ALTER FRAGMENT	DROP TABLE
ALTER INDEX	DROP TRIGGER
ALTER TABLE	DROP VIEW
BEGIN WORK	INSERT
COMMIT WORK	RENAME COLUMN
CREATE TRIGGER	RENAME TABLE
DELETE	ROLLBACK WORK
DROP DATABASE	SET CONSTRAINTS
DROP INDEX	UPDATE
DROP SYNONYM

For example, if you use the following INSERT statement, the execution of the called procedure dup_name is restricted:

CREATE PROCEDURE sp_insert ()

.

.

.

INSERT INTO q_customer 

	VALUES (SELECT * FROM customer

		WHERE dup_name ('lname') = 2)

.

.

.

END PROCEDURE;

In this example, dup_name cannot execute the statements listed in Figure 1-9. However, if dup_name is called within a statement that is not an INSERT, UPDATE, SELECT, or DELETE statement (namely EXECUTE PROCEDURE), dup_name can execute the statements listed in Figure 1-9.

You can use the BEGIN WORK and COMMIT WORK statements in procedures. You can start a transaction, finish a transaction, or start and finish a transaction in a procedure. If you start a transaction in a procedure that is executed remotely, you must finish the transaction before the procedure exits.

Warning: When you specify a date value in an expression in any statement in the statement block, make sure to specify 4 digits instead of 2 digits for the year. When you specify a 4-digit year, the DBCENTURY environment variable has no effect on how the database server interprets the date value. When you specify a 2-digit year, the DBCENTURY environment variable can affect how the database server interprets the date value, so the routine might produce unpredictable results. See the "Informix Guide to SQL: Syntax" for more information on the DBCENTURY environment variable.

Adding Comments to an SPL Routine

To add a comment to any line of a routine, place a double-dash (--) before the comment or enclose the comment in braces ({}). The double dash complies with the ANSI standard. The curly brackets are an Informix extension to the ANSI standard.

References

In this manual, see the CREATE FUNCTION and CREATE PROCEDURE statements.

In the Informix Guide to SQL: Tutorial, see Chapter 14 for information about how to create and use SPL routines.

Synonym Name

The Synonym Name segment specifies the name of a synonym. Use the Synonym Name segment whenever you see a reference to a synonym name in a syntax diagram.

Syntax

Element	Purpose	Restrictions	Syntax
database	The name of the database where the synonym resides	The database must exist.	Database Name, p. 1-856
dbservername	The name of the Universal Server database server that is home to database. The @ symbol is a literal character that introduces the database server name.	The database server specified in dbservername must match the name of a database server in the sqlhosts file.	Database Name, p. 1-856
owner	The user name of the owner of the synonym	If you are using an ANSI-compliant database, you must specify the owner for a synonym that you do not own. If you put quotation marks around the name that you enter in owner, the name is stored exactly as typed. If you do not put quotation marks around the name that you enter in owner, the name is stored as uppercase letters.	The user name must conform to the conventions of your operating system.

Usage

The actual name of the synonym is an SQL identifier.

GLS

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

If you are creating the synonym, the name of the synonym must be unique within a database. The name cannot be the same as table names, temporary table names, or view names. It is possible to have a public and private synonym with the same name.

ANSI

If you are creating the synonym, the combination owner.name must be unique within a database.

The owner name is case sensitive. In an ANSI-compliant database, if you do not use quotes around the owner name, the name of the table owner is stored in uppercase letters. For more information, see the discussion of case sensitivity in ANSI-compliant databases on page 1-1049.

References

See the CREATE SYNONYM statement in this manual for information on creating synonyms.

In the Informix Guide to SQL: Tutorial, see the discussion of synonyms in Chapter 11.

Table Name

The Table Name segment specifies the name of a table. Use the Table Name segment whenever you see a reference to a table name in a syntax diagram.

Syntax

Element	Purpose	Restrictions	Syntax
database	The name of the database where the table resides	The database must exist.	Database Name, p. 1-856
dbservername	The name of the Universal Server database server that is home to database. The @ symbol is a literal character that introduces the database server name.	The database server that is specified in dbservername must match the name of a database server in the sqlhosts file.	Database Name, p. 1-856
owner	The user name of the owner of the table	If you are using an ANSI-compliant database, you must specify the owner for a table that you do not own. If you put quotation marks around the name that you enter in owner, the name is stored exactly as typed. If you do not put quotation marks around the name that you enter in owner, the name is stored as uppercase letters. In SELECT statements and other statements that access tables in an ANSI-compliant database, the table owner that you specify must exactly match the actual owner of the table. See "Case Sensitivity in ANSI-Compliant Databases" for further information on this restriction.	The user name must conform to the conventions of your operating system.

Usage

The name of a table is an SQL identifier. The following example shows a table specification:

empinfo@personnel:emp_names 

GLS

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

If you are creating or renaming a table, the name of the table must be unique among all the tables, synonyms, temporary tables, and views that already exist in the database.

ANSI

If you are creating or renaming a table, you must make sure that the combination of owner and name is unique within a database.

In an ANSI-compliant database, the table name must include owner. unless you are the owner. For system catalog tables, the owner is informix.

Case Sensitivity in ANSI-Compliant Databases

ANSI

The database server shifts the owner name to uppercase letters before the statement executes, unless the owner name is enclosed in quotes. Put quotes around the owner portion of a name if you want the owner to be read exactly as written. In the following example, the name cathl in the first statement is upshifted to CATHL before it is used; the name nancy in the second statement is not upshifted:

SELECT * FROM cathl.customer



SELECT * FROM 'nancy'.customer

No problem exists if you create a table with an implicit owner in uppercase letters and the owner's real login name is also in uppercase letters. For example, suppose that you are the user BROWN, and you create a view with the following statement:

CREATE VIEW newcust AS

	SELECT fname, lname FROM customer WHERE state = 'NJ'

You, BROWN, can run the following SELECT statements on the view:

SELECT * FROM brown.newcust



SELECT * FROM newcust



SELECT * FROM systables WHERE tabname = newcust

	AND owner = USER

In the first query in the preceding example, the database server automatically upshifts brown before the SELECT statement executes. In the second query, the database server returns the owner name BROWN already upshifted. In the third query, USER returns the login name as it is stored-in this case, in uppercase letters. If you are the user nancy, and you use the following statement, the resulting view has the name NANCY.njcust:

CREATE VIEW nancy.njcust AS

	SELECT fname, lname FROM customer WHERE state = 'NJ'

If you are nancy, and you use the following statement, the resulting view has the name nancy.njcust:

CREATE VIEW 'nancy'.njcust AS

	SELECT fname, lname FROM customer WHERE state = 'NJ'

The following SELECT statement fails because it tries to match the name NANCY.njcust to the actual owner and table name of nancy.njcust:

SELECT * FROM nancy.njcust 

References

See the CREATE TABLE statement in this manual for information on creating tables.

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

View Name

The View Name segment specifies the name of a view. Use the View Name segment whenever you see a reference to a view name in a syntax diagram.

Syntax

Element	Purpose	Restrictions	Syntax
database	The name of the database where the view resides	The database must exist.	Database Name, p. 1-856
dbservername	The name of the Universal Server database server that is home to database. The @ symbol is a literal character that introduces the database server name.	The database server that is specified in dbservername must match the name of a database server in the sqlhosts file.	Database Name, p. 1-856
owner	The user name of the owner of the view	If you are using an ANSI-compliant database, you must specify the owner for a view that you do not own. If you put quotation marks around the name you enter in owner, the name is stored exactly as typed. If you do not put quotation marks around the name that you enter in owner, the name is stored as uppercase letters.	The user name must conform to the conventions of your operating system.

Usage

The name of a view is an SQL identifier.

GLS

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

The use of the prefix owner. is optional; however, if you use it, the database server does check owner for accuracy. If you are creating a view, the name of the view must be unique among all the tables, synonyms, temporary tables, and views that already exist in the database.

ANSI

If you are creating a view, the owner.view-name must be unique among all the tables, synonyms, and views that already exist in the database.

The owner name is case sensitive. In an ANSI-compliant database, if you do not use quotes around the owner name, the name of the table owner is stored as uppercase letters. For more information, see the discussion of case sensitivity in ANSI-compliant databases on page 1-1049.

References

See the CREATE VIEW statement in this manual for information about how to create views.

In the Informix Guide to SQL: Tutorial, see the discussions of views in Chapter 11.