Working with SQL in PSQL


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.