Working with SQL in PSQL
Structured Query Language (SQL) is a database language that uses Englishlike statements to perform database operations. Both the American National Standards Institute (ANSI) and IBM have defined standards for SQL. The IBM standard is the Systems Application Architecture (SAA). The PSQL product implements most of the features of both ANSI SQL and IBM SAA SQL and provides additional extensions that neither standard specifies.
PSQL allows you to create different types of SQL statements. The following table lists the types of SQL statements you can create and the tasks you can accomplish using each type of statement:
Table 1
SQL Statement Types and Related Tasks
SQL Statement Type
Tasks
Data Definition
Create, modify, and delete tables.
Create and delete views.
Create and delete indexes.
Create and delete stored SQL procedures.
Create and delete triggers.
Create and delete user-defined functions.
Data Manipulation
Retrieve, insert, update, and delete data in tables.
Define transactions.
Define and delete views.
Execute stored SQL procedures.
Execute triggers.
Data Control
Enable and disable security for a dictionary.
Create and delete users.
Add and drop users from groups.
Change user passwords.
Grant and revoke table access rights.
The rest of this chapter briefly describes the SQL statements used in each statement category. For detailed information about each statement, refer to
SQL Syntax Reference
.
The following are the statement category overview sections found in this section:
•
Data Definition Statements
•
Data Manipulation Statements
•
Data Control Statements
Data Definition Statements
Data definition statements let you specify the characteristics of your database. When you execute data definition statements, PSQL stores the description of your database in a data dictionary. You must define your database in the dictionary before you can store or retrieve information.
PSQL allows you to construct data definition statements to do the following:
•
Create, modify, and delete tables.
•
Create and delete views.
•
Create and delete indexes.
•
Create and delete triggers.
•
Create and delete stored procedures.
•
Create and delete user-defined functions.
The following sections briefly describe the SQL statements associated with each of these tasks. For general information about defining the characteristics of your database, refer to the
PSQL Programmer's Guide
in the Developer Reference.
Creating, Modifying, and Deleting Tables
You can create, modify, and delete tables from a database by constructing SQL using the following statements:
Table 2
Data Definition Statements – Tables
CREATE TABLE
Defines a table and optionally creates the corresponding data file.
ALTER TABLE
Changes a table definition. With an ALTER TABLE statement, you can perform such actions as add a column to the table definition, remove a column from the table definition, change a column’s data type or length (or other characteristics), and add or remove a primary key or a foreign key and associate the table definition with an different data file.
DROP TABLE
Deletes a table from the data dictionary and optionally deletes the associated data file from the disk.
Creating and Deleting Views
You can create and delete views from a database by constructing SQL using the following statements:
Table 3
Data Definition Statements – Views
CREATE VIEW
Defines a new view.
DROP VIEW
Deletes a view.
Creating and Deleting Indexes
You can create and delete indexes from a database by constructing SQL using the following statements:
Table 4
Data Definition Statements – Indexes
CREATE INDEX
Defines a new index (a named index) for an existing table.
DROP INDEX
Deletes a named index.
Creating and Deleting Triggers
You can create and delete triggers from a database by constructing SQL using the following statements:
Table 5
Data Definition Statements – Triggers
CREATE TRIGGER
Defines a trigger for an existing table.
DROP TRIGGER
Deletes a trigger.
PSQL provides additional SQL control statements, which you can only use in the body of a trigger. You can use the following statements in triggers:
Table 6
Data Definition Statements – Trigger Control
BEFORE
Defines the trigger execution before the INSERT, UPDATE, or DELETE operation.
AFTER
Defines the trigger execution after the INSERT, UPDATE, or DELETE operation.
Creating and Deleting Stored Procedures
A stored procedure consists of statements you can precompile and save in the dictionary. To create and delete stored procedures, construct statements using the following:
Table 7
Data Definition Statements – Stored Procedure
CREATE PROCEDURE
Stores a new procedure in the data dictionary.
DROP PROCEDURE
Deletes a stored procedure from the data dictionary.
PSQL provides additional SQL control statements, which you can only use in the body of a stored procedure. You can use the following statements in stored procedures:
Table 8
Data Definition Statements – Stored Procedure Control
IF
...THEN...ELSE
Provides conditional execution based on the truth value of a condition.
LEAVE
Continues execution by leaving a block or loop statement.
LOOP
Repeats the execution of a block of statements.
WHILE
Repeats the execution of a block of statements while a specified condition is true.
Creating and Deleting User-Defined Functions (UDF)
In addition to the built-in functions, PSQL allows you to create your own user-defined functions (UDF) and use them in PSQL statements.
A user-defined function is a database object that encapsulates one or more PSQL statements that can be reused. A user-defined function takes zero or more input arguments and evaluates a return value, which is a scalar value.
User-defined functions are always defined within the context of a database. Successful execution of this statement results in the storing of the UDF definition in the specific database. Once stored, the UDF can be modified, invoked, and deleted.
PSQL supports scalar user-defined functions.
A scalar user-defined function returns a single value of the data type specified in the RETURNS clause of the PSQL statement. A scalar UDF can contain multiple PSQL statements. You can specify any data type value for the returned data except text, ntext, image, cursor, or timestamp.
To create and delete user-defined functions, construct statements using the following:
Table 9
Data Definition Statements – User-Defined Function
CREATE FUNCTION
Creates a scalar user-defined function in the database.
DROP FUNCTION
Deletes a scalar user-defined function from the database.
Data Manipulation Statements
Data manipulation statements let you access and modify the contents of your database. PSQL allows you to construct data manipulation statements to do the following:
•
Retrieve data from tables.
•
Modify data in tables.
•
Define transactions.
•
Create and delete views.
•
Execute stored procedures.
•
Execute triggers.
The following sections briefly describe the SQL statements associated with each of these tasks.
Retrieving Data
All statements you use to retrieve information from a database are based on the SELECT statement.
Table 10
Data Manipulation Statements – Retrieving Data
SELECT
Retrieves data from one or more tables in the database.
When you create a SELECT statement, you can use various clauses to specify different options. The types of clauses you use in a SELECT statement are as follows:
Table 11
Data Manipulation Statements – Retrieving Data Options
FROM
Specifies the tables or views from which to retrieve data.
WHERE
Defines search criteria that qualify the data a SELECT statement retrieves.
GROUP BY
Combines sets of rows according to the criteria you specify and allows you to determine aggregate values for one or more columns in a group.
HAVING
Allows you to limit a view by specifying criteria that the aggregate values of a group must meet.
ORDER BY
Determines the order in which PSQL returns selected rows.
In addition, you can use the UNION keyword to obtain a single result table from multiple SELECT queries.
Modifying Data
You can add, change, or delete data from tables and views by issuing statements such as the following:
Table 12
Data Manipulation Statements – Modifying Data
INSERT
Adds rows to one or more tables or a view.
UPDATE
Changes data in a table or a view.
DELETE
Deletes rows from a table or a view.
When you create a DELETE or UPDATE statement, you can use a WHERE clause to define search criteria that restrict the data upon which the statement acts.
Creating and Deleting Views
You can create and delete views by constructing SQL using the following statements:
Table 13
Data Manipulation Statements – Views
CREATE VIEW
Defines a database view and stores the definition in the dictionary.
DROP VIEW
Deletes a view from the data dictionary.
Executing Stored Procedures
A stored procedure consists of statements that you can precompile and save in the dictionary. To execute stored procedures, construct statements using the following:
Table 14
Data Manipulation Statements- Stored Procedures
CALL
or EXEC[UTE]
Recalls a previously compiled procedure and executes it.
Executing System Stored Procedures
A system stored procedure helps you accomplish those administrative and informative tasks that are not covered by the Data Definition Language. The system stored procedures have a
psp_
prefix. To execute stored procedures, construct statements using the following:
Table 15
Data Manipulation Statements – System Stored Procedures
CALL
or EXEC[UTE]
Recalls a system stored procedure and executes it.
For more details, see
System Stored Procedures
.
Executing Triggers
A trigger consists of statements you can precompile and save in the dictionary. Triggers are executed automatically by the engine when the specified conditions occur.
Data Control Statements
Data control statements let you define security for your database. When you create a dictionary, no security is defined for it until you explicitly enable security for that dictionary. PSQL allows you to construct data control statements to do the following:
•
Enable and disable security.
•
Create and delete users and groups.
•
Add and drop users from groups and change user passwords.
•
Grant and revoke rights.
Note:
If you have a Btrieve owner name set on a file that is a table in a secure database, the Master user of the database must use the owner name in any GRANT statement to grant privileges on the given table to any user, including the Master user.
The following sections briefly describe the SQL statements associated with each of these tasks.
Enabling and Disabling Security
You can enable or disable security for a database by issuing statements using the following statement:
Table 16
Data Control Statements – Security
SET SECURITY
Enables or disables security for the database and sets the Master password.
Creating and Deleting Users and Groups
You can create or delete users and user groups for the database by constructing SQL using the following statements:
Table 17
Data Control Statements – Groups and Users
ALTER USER
Rename a user or change a user’s password.
CREATE USER
Creates a new user with or without a password or membership in a group.
DROP USER
Delete a user.
ALTER GROUP
Adds users to a group. Drops users from a group.
CREATE GROUP
Creates a new group of users.
DROP GROUP
Deletes a group of users.
GRANT LOGIN TO
Creates users and passwords, or adds users to groups.
REVOKE LOGIN FROM
Removes a user from the dictionary.
Granting and Revoking Rights
You can assign or remove rights from users or groups by issuing statements using the following:
Table 18
Data Control Statements – Rights
GRANT (access rights)
Grants a specific type of rights to a user or a group. The rights you can grant with a GRANT (access rights) statement are All, Insert, Delete, Alter, Select, Update, and References.
GRANT CREATETAB TO
Grants the right to create tables to a user or a group.
REVOKE (access rights)
Revokes access rights from a user or a group.
REVOKE CREATETAB FROM
Revokes the right to create tables from a user or a group.