As mentioned in the previous sections, when the command initdb is executed to create the Database system, the System Catalogs are also created under the $PGHOME directory, as well as under the special database named template1 which will form the skeleton for other databases created under the databasesystem.
To install a procedural language in Postgres the createlang command available from the shell is used, in order to use this command it is recommended that the PGLIB environment variable be set, this variable should point to the: /usr/local/pgsql/lib directory (the Postgres libraries), the variable can also be set with a flag (-L) at createlang execution. It should be noted that by default no procedural language is active on any database,NOTE: active; the difference between active and installed is that the installation refers to the installation-library level, while the activation is done at the more granular database level, this will be made clearer in the following sections.
Install (Activate) PLPGSQL
In order to activate the PLPGSQL (Procedural Language PG(Postgres)SQL) the command createlang will require at a mininmum two parameters, the library location (provided with the $PGLIB variable or -L flag) and the database (provided with the $DBNAME or -d flag), upon execution, the language should be typed in at the prompt:
[postgres@servidor1 db]$ createlang -d workload Language to install in database workload: plpgsql [postgres@servidor1 db]$ |
Install (Activate) PLTCL & PLPERL
To activate these procedural languages the process is a little more involved than for PLPGSQL, this is due to the fact that they need to be installed in the system ; these procedural languages requiere the shared libraries pltcl.so and plperl.so , the default installation/compile does not create these shared libraries, so they will have to be installed in the /usr/local/pgsql/lib directory. To do this there are two possibilities:
--with-tcl flag. (This in no way affects existing databasesystems or databases) /src/pl locate the perl and tcl directories, these will contain the source (.c) for obtaining the pltcl.so and plperl.so libraries, these should then be placed in the /usr/local/pgsql/lib library.To successfully install the pltcl.so and plperl.so correctly, the system you are installing Postgres on will have to have the appropriate Tcl Interpreter and Perl Interpreter , this is necessary because Postgres does not come with a procedural language environment so in order for procedural languages to be executed it will rely on the "Host's" installed packages.
Activate Languages by Default
In order to activate languages by default on a new database, this can be done by activating the desired languages in the template1 database located under the $PGDATA/base directory, this is made possible because every newly created database generates its base tables from this template database
Every database in a databasesystem contains a table named: pg_language, this table contains al the active procedural languages for the database. This table can be queried directly from the shell with the createlang -d workload -l command, it would display the following:
[postgres@servidor1 postgres]$ createlang -d workload -l
Procedural languages
Name | Trusted? | Compiler
---------+----------+----------
plpgsql | t | PL/pgSQL
pltcl | t | PL/Tcl
(2 rows)
|
Information about the defined functions in a database can be queried with the \df (regexp) command in PSQL (Postgres's SQL*Plus), the \df+ (regexp) can also be used to display more detailed information like language (plperl,pltcl..) and description. If no (regexp) is provided all functions will be returned, if specified it returns those functions matching the regexp.
A trigger is executed implicitly whenever the triggering event happens,and the trigger doesnt accept arguments. Triggers are used to:
The general syntax is :
CREATE TRIGGER trigger_name [BEFORE | AFTER] [INSERT| DELETE | UPDATE] triggering_event ON table_reference FOR EACH [ROW] EXECUTE PROCEDURE procedure (args);
Where triggering_event specifies when the trigger fires, table_reference is the table for which the trigger is defined, and procedure (args) is the main code for the trigger.
Compared to Oracle, Postgres cannot define the trigger event or action directly in the declaration, Postgres depends on creating a separate function to be executed on the trigger, this has the disadvantage of splitting the trigger logic in various parts, but it has the advantage of calling any triggering action (function) within Postgres support: Perl, Tcl or built-in Sql.
TYPES OF TRIGGERS
The triggering event determines the type of the trigger. Triggers can be defined for INSERT, UPDATE, or DELETE operations. They can be fired before or after the operation, in the current version Postgres (7.0) they can only be fired on row operations (Oracle allows statement operations). The values for the statement, timing and level determine the type of the trigger. There are a total of 6 possible types: 3 statements,2 timing and 1 level.
| Category | Values | Comments |
|---|---|---|
| Statement | INSERT, UPDATE, DELETE | Defines which kind of DML statements causes the trigger to fire |
| Timing | BEFORE or AFTER | Defines wether the trigger fires before the statement is executed or after the statement is executed |
| Level | Row (Statement not currently supported) | If the trigger is a row-level trigger, it fires once for each row affected by the triggering statement. If the trigger is a statement-level trigger (not supported) it fires once , either before or after the statement. A row-level trigger is identified by the FOR EACH ROW clause in the trigger definition |
You can drop the trigger with the following commands:
DROP TRIGGER [name] on [table name] <trigger_name>
NOTE: There is no command for enabling|disabling a trigger in Postgres like Oracle.
To display the triggers defined in a database you have to query the object descriptors with the argument "triggers", from PSQL: \dd triggers
To the extent the author knows, there is nothing similar to a control file in Postgres , all information regarding the physical structure (file location) of a database is located in the system catalogs.
Other than the fact that Postgres complies with the "readers wait for writers" database adage, that is, readers will consistently see only commited data , and writers must wait for any other data that is locked or under transaction, it is not clear if the transaction history made on the database is or can be piped out to a particular file and later reapplied , to conform what would in "Oracle-speak" be called a Redo-Log file, facilitating the rollback to a certain transaction or time functionality.
This issue brings up another interesting question, since all uncommited data is kept in memory,what happens if a long query/transaction is executed and the memory is exhausted or overwritten by another transaction, what happens if a user tries to query this overwritten data, will a SNAPSHOT TO OLD appear like Oracle, is there any manner to write this out to a file before its overwritten ? Basically creating an online redo-log file ? If you know how, please email me so I can add it here.
There is no such logical structure in a Postgres database, everything is kept directly in system files, this would be similar to having Oracle data files , but without any extra logical divisions (a.k.a tablespaces)