Programming
Database lectures for mathematics students
Zbigniew Jurkiewicz, Institute of Informatics UW
April 25, 2015
Functions
Functions are defined in Postgres with
CREATE FUNCTION name(parameter type, ...) RETURNS result-type AS $$
function-body
$$ LANGUAGE language-name;
For the SQL language the body is a sequence of SQL statements.
The function body in older versions was surrounded by single quotes, which was inconvenient, as it lead to
doubling any apostrophes inside body (around strings etc.).
Functions
Because in Postgres the statement SELECT can be used for evaluating expressions not connected with any database, e.g.
bd> SELECT 2 + 5 AS seven;
seven --- 7
so simple functions can be defined using just SQL:
CREATE FUNCTION add7(i int4) RETURNS int4 AS $$
SELECT i + 7;
$$ LANGUAGE sql;
Functions
Example call of the above function:
bd> SELECT add7(4) AS result;
result --- 11
Functions
Functions written in SQL return as their value the result from the last statement in the body, this is usually SELECT.
If the result is not to be interpreted as a set, the first resulting row will be taken (so take care about possible ORDER BY;-).
If the query result is empty, NULL is returned.
Functions
If an SQL function does not return anything reasonable, the result type should be declared as void, and the body may not terminate with SELECT query.
CREATE FUNCTION clean () RETURNS void AS $$
DELETE FROM Animals WHERE weight <= 0;
$$ LANGUAGE SQL;
SELECT clean();
PL/pgSQL
Usually the stored database procedures are not defined in SQL, but in procedural languages.
In Postgres the default language for database server is PL/pgSQL, similar to PL/SQL from Oracle.
The standard languages provided in distribution are installed using the shell script createlang.
To install PL/pgSQL in the database template1 it is enough to type
createlang plpgsql template1
Removal of a procedural language could be done within SQL using the statement DROP LANGUAGE or by running
PL/pgSQL
PL/pgSQL (Procedural Language/postgresSQL) extends SQL with typical constructs encountered in imperative programming languages.
The basic unit in PL/SQL is the block, programs are built from nested blocks.
Inside blocks you may use as instructions all SQL data manipulation statement (e.g. SELECT, INSERT, UPDATE, DELETE) and transaction control statements.
There is an extended form of SELECT statement, to place in PL/pgSQL variables the values found in the database.
Schema data definition instructions, such as CREATE, DROPor ALTER, are not allowed.
PL/pgSQL
In addition to SQL statements blocks may contain typical instructions like assignments, conditional instructions, loops or procedure calls.
PL/pgSQL blocks are usually placed in function bodies, and this functions can be called by using ordinary SELECT from psql program.
Blocks
The syntax of a PL/pgSQL block is as follows:
[DECLARE
declarations of variables, constants and local procedures]
BEGIN instructions END;
(the square brackets mark the optional part, they are not syntax elements).
Blocks
Example block:
DECLARE
a NUMERIC(5);
BEGIN
SELECT COUNT(*) INTO a FROM EMP
WHERE ENAME LIKE ’A%’;
IF a > 0 THEN
INSERT INTO TEMP VALUES(a);
END IF;
RAISE NOTICE ’OK’;
END;
Triggers
Triggers are procedures “automagically” called when some event happens, e.g. when inserting a new row into a particular table.
Triggers originally were not intended to serve as a mechanism for ensuring the legality of database states (this role was to be played by integrity constraints and assertions), but to ensure the legality of transitions between states.
But since no popular DBMS implement assertions (it is not easy, by the way), triggers are most often used to realize complex constraints.
Triggers
Trigger definition contains three components
Event: modification of some table, e.g. “inserting into Species table”.
Condition: Boolean expression in SQL syntax.
Actions: instructions to be executed, most often written in SQL or PL/SQL.
Syntax of triggers
According to SQL standard, the trigger syntax is as follows:
CREATE [OR REPLACE] TRIGGER name
{BEFORE | AFTER} INSERT OR DELETE OR UPDATE ON table
FOR EACH {ROW | STATEMENT}
BEGIN
instruction ...
END;
Example
Let’s try to write a trigger which after removing a row from Species table would set NULLs in the species column for corresponding rows of Animals table.
We can of course achieve the same effect more easily using just foreign key constraints — but it may be interesting to see how they can be implemented.
Example
CREATE TRIGGER DelSpec AFTER DELETE ON Species FOR EACH ROW EXECUTE UPDATE Animals
SET species = NULL
WHERE species = OLD.species;
Other example
Let us assume that for each species in the table Species we want to store (in a separate column) the number of animals of this kind.
We start by adding the appropriate column ALTER Species
ADD howmany INTEGER DEFAULT 0 CHECK (howmany >= 0);
Other example
Time to define triggers
CREATE TRIGGER InsAnimals AFTER INSERT ON Animals FOR EACH ROW EXECUTE UPDATE Species
SET howmany = howmany + 1 WHERE species = NEW.species;
CREATE TRIGGER DelAnimals AFTER DELETE ON Animals FOR EACH ROW EXECUTE UPDATE Species
SET howmany = howmany - 1 WHERE species = OLD.species;
Triggers in Postgres
Triggers in PL/pgSQL always contain as their bodies a call to a previously defined procedure. The procedure should be a function with no arguments and should declare that it returns the special type TRIGGER
The trigger function receives data not from ordinary functional parameters, but through the structure TriggerData.
Trigger procedures should not be called directly (by name).
They are called implicitly by trigger, whenever the associated events occur.
The event can be one of SQL operations INSERT, DELETE or UPDATE.
Triggers in Postgres
For each trigger you must define whether its procedure will be called BEFORE or AFTER the operation proper.
Also each trigger should have declared one of two levels:
row or statement. The option FOR EACH ROW declares row-level trigger, by default the trigger is statement-level.
Statement-level trigger is fired only once for the whole SQL statement, but row-level trigger is independently fired once for each row being modified.
Triggers in Postgres
The trigger definition in PL/pgSQL has the following syntax:
CREATE TRIGGER name
BEFORE | AFTER INSERT | DELETE | UPDATE [OR ...]
ON table
[FOR EACH ROW | STATEMENT]
EXECUTE PROCEDURE procedure-name(arguments);
Triggers in Postgres
Additional remarks:
Tthe trigger function must be defined before creating trigger.
The name of a trigger must be unique for a given table and is used when removing the trigger.
For a single trigger one can state up to three kinds of events (INSERT, DELETE or UPDATE) activating the trigger (use OR connective between them). Examples:
... INSERT ON R ...
... INSERT OR DELETE OR UPDATE ON R ...
Triggers in Postgres
When removing a trigger it is necessary to give the table name in addition to the trigger name.
DROP TRIGGER name ON table;
In functions associated with row-level triggers there are two predeclared record variables in function bodies: NEW and OLD, they have as values old and new contents of the row.
The trigger procedure for insertion and modification should return the appropriate record, usually it is just NEW.
It can also return NULL. For triggers of the type BEFORE this means that the execution failed and the operation should be silently ignored.
Triggers in Postgres
The triggers of the same type are run in the alphabetic order according to their names.
If a trigger procedure uses SQL data modification statements, they can cause firing other triggers. This situation is called trigger cascading.
There is no limit to the number of levels for cascading triggers, in particular the recursive calls are possible.
The programmer is fully responsible for avoiding an infinite recursion.
Triggers in Postgres
Time for examples. Assume we have the table
CREATE TABLE Species (
name VARCHAR(30) PRIMARY KEY, continent VARCHAR(11),
preserved BOOLEAN, food VARCHAR(15) );
Triggers in Postgres
Using trigger we will ensure that the name of the continent will always start with a capital letter:
CREATE FUNCTION normcont () RETURNS TRIGGER AS $$
BEGIN
IF NEW.continent IS NOT NULL THEN
NEW.continent := lower(NEW.continent);
NEW.continent := initcap(NEW.continent);
END IF;
RETURN NEW;
END;
$$ LANGUAGE ’plpgsql’;
CREATE TRIGGER spectrig1
BEFORE INSERT OR UPDATE ON Species FOR EACH ROW
EXECUTE PROCEDURE normcont();
Triggers in Postgres
The execution of the statement CREATE TRIGGER only creates a trigger, but does not fire it.
To fire a trigger we must cause an occurence of the
appropriate event, in this case row insertion or modification for the table Species.
UPDATE Species
SET continent=’africa’
WHERE name=’hen’;
Application programming
The ordinary users access the database by running programs, executed on the user’s workstation (client programs).
Such programs can be written in any programming
language, but the language implementation must provide a programmer interface for SQL (called sometimes API — Application Programmer Interface).
This interface is sometimes called binding and could be specific for a given DBMS or universal (like ODBC, JDBC), i.e. covering any SQL database.
Interface for C programming language
Implementors of a DBMS nearly always provide a basic procedural interface for C programming language. The SQL standard calls such type of interface CLI or Call-Level Interface
Let us look briefly at the PostgreSQL interface for C language, contained in the library libpq.
The application should first attach the header file:
#include "libpq-fe.h"
Interface for C programming language
Before using the database the connection has to be established:
PGConn *connection;
connection =
PQconnectdb("dbname=bd,host=labdb,user=me");
if (PQstatus(connection) == CONNECTION_BAD) fprintf(stderr, "No connection\n");
PQfinish(connection);
exit(1);
Assume the connection succeeded and we want now to look into a table Species, which contains columns name and continent.
Interface for C programming language
We want to find out a continent, which is the origin of the species with the given name (e.g. read from a keybord).
PGresult *result;
result = PQexec(connection,
"SELECT continent FROM Species "
"WHERE species = ’racoon’");
if (PQresultStatus(result) == PGRES_TUPLES_OK &&
PQntuples(result) == 1)
printf("Racoon comes from %s\n", PQgetvalue(result, 0, 0));
else
fprintf(stderr, "No answer\n");
Embedded SQL
The embedded SQL is the technique of directly entering SQL statements as instructions of the host language (in our case the C language).
In PostgreSQL we can use the tool called ECPG.
When embedding SQL into other programming languages there are many problems to be solved, the most important being
Different data types in SQL than in the embedding language — conversion is necessary.
SQL is oriented towards operating on sets of values, not with single values — usual solution is to use cursors.
Embedded SQL
In PostgreSQL the program in C with embedded SQL statements must be first preprocessed with ecpg into a program in pure C.
The preprocessor finds the SQL statements contained in program and replaces them by appropriate function calls from CLI libraries.
A preprocessed program is then compiled using the ordinary C compiler to obtain the executable binary.
ecpg -I/usr/include/ecpg test1.pgc
cc -I/usr/include/ecpg -o test1 test1.c -lecpg
Embedded SQL
All SQL statements must be preceded by EXEC SQL and terminated with semicolon (“;”).
They can be inserted anywhere in C program, ensuring only that all declarations precede executable statements.
An example:
int main () {
EXEC SQL BEGIN DECLARE SECTION;
int w;
EXEC SQL END DECLARE SECTION;
...
EXEC SQL SELECT age INTO :w FROM Animals
WHERE name=’Kropka’;
...
printf("Kropka weights %d kilo\n", w);
...
