|
This section contains the following topics: |
|
|
Ingres 2006 Release 2 enhancements to the DBMS Server allow for better integration by Ingres partners. New features for application developers include:
|
|
A derived table results when you code a SELECT in the FROM clause of a SELECT or UPDATE statement.
Derived tables let you create or simplify complex queries. Useful in data warehousing applications, they provide a way to isolate complex portions of query syntax from the rest of a query.
Some complex queries cannot be implemented without using either pre-defined views or derived tables. The derived table behaves like an inline view, but is more concise and avoids having to define persistent objects that may be used for a single query only.
For details on derived tables, see the SQL Reference Guide.
|
|
Ingres now supports the ANSI date and time data types DATE, TIME, TIMESTAMP, and INTERVAL. This enhancement makes it easier for applications to migrate to Ingres from a non-Ingres database.
Previously, Ingres supported one date data type that could store dates, times, intervals, and time stamps. The previous date type is renamed to INGRESDATE.
The configuration parameter date_type_alias controls whether the keyword DATE used for a column data type refers to INGRESDATE or to ANSIDATE. If this parameter is not set, then the DATE keyword cannot be used in SQL statements.
When migrating from an earlier version of Ingres, the existing date data in the database is not affected. The data is still a valid INGRESDATE data type.
If you use the new date format (ANSIDATE), existing scripts and database procedures with the old DATE column definitions may need to be changed.
Note: When installing or upgrading Ingres using a non interactive install and no value is provided in the response file for II_DATE_TYPE_ALIAS, the value defaults to INGRESDATE.
For details, see the SQL Reference Guide.
|
|
BEFORE triggers let an application call an Ingres database procedure before a triggering operation (INSERT, UPDATE, or DELETE) is executed. The procedure can change the values of columns in rows being inserted or updated, or can inhibit the deletion of rows, depending on their contents. In the CREATE RULE statement, the keyword BEFORE can be used in defining the table condition that triggers the rule.
In addition, in the CREATE PROCEDURE statement, you can optionally assign formal parameters a mode, IN, OUT, or INOUT. For OUT and INOUT parameters, modified parameter values can be passed back to the calling procedure or triggering operation.
For details, see the SQL Reference Guide and Database Administrator Guide.
|
|
To ease application migration, Ingres SQL supports the DESCRIBE INPUT statement. This statement obtains the number and type of input parameters of a prepared statement. Such information is necessary for products that support user-supplied queries.
For details, see the SQL Reference Guide.
|
|
Ingres supports indexes on temporary tables. This feature can be used on global temporary tables for more efficient access.
For more information, see the SQL Reference Guide.
|
|
This release introduces a new syntax for referencing global temporary tables. This enhancement facilitates porting applications to Ingres from other database architectures.
The new syntax drops the requirement of prefixing a temporary table name with the SESSION qualifier in a DECLARE GLOBAL TEMPORARY TABLE statement. If the SESSION schema qualifier is omitted in the declaration, all subsequent DML and DDL statements referencing the table can optionally omit it. When this syntax is used, creating temporary and permanent tables with the same name is not allowed, to avoid confusion in referencing tables.
Using the "SESSION." schema qualifier when referencing temporary tables is still required if the DECLARE GLOBAL TEMPORARY TABLE statement includes the SESSION qualifier in the table name. When this syntax is used, temporary and permanent tables can have the same name.
For more information, see the Database Administrator Guide and SQL Reference Guide.
|
|
A column can be defined that automatically takes an increasing sequence value when one is not provided. This feature can be used to create surrogate keys.
For more information, see the SQL Reference Guide.
|
|
The DBMS Server performs automatic coercion between integer/float data types and char, varchar, nchar, or nvarchar data types. For example, an INSERT can place a quoted string value into an integer column or a database procedure can have an assignment statement that assigns a float value to a char variable—all without using explicit coercion functions such as CHAR() or INT4().
|
|
The following date functions extract the specified portion of a date or timestamp: year(), quarter(), month(), week(), week_iso(), day(), hour(), minute(), second(), and microsecond(). For example, year('2006-12-15') returns 2006.
For more information, see the SQL Reference Guide.