Procedural Languages

Procedural Languages Overview

• PostgreSQL allows user-defined functions to be written in a variety of procedural languages. 
• The database server has no built-in knowledge about how to interpret the function's source text. 
• Instead, the task is passed to a handler that knows the details of that particular language.
• PostgreSQL currently supports several standard procedural languages :
• PL/pgSQL
• PL/Tcl
• PL/Perl
• PL/Python
• PL/Java
• PL/Ruby
• Other languages can be defined by users

• PL/pgSQL is a loadable procedural language for the PostgreSQL database system.
• PL/pgSQL has several distinct features :
• Can be used to create functions and trigger procedures,
• Adds control structures to the SQL language,
• Can perform complex computations,
• Inherits all user-defined types, functions, and operators,
• Can be defined to be trusted by the server,
• Is easy to use.
  

Indexes

An index:

• Is a schema object
• Can be used by the PostgreSQL server to speed up the retrieval of rows by using a pointer
• Can reduce disk I/O by using a rapid path access method to locate data quickly
• Is independent of the table that it indexes
• Is used and maintained automatically by the PostgreSQL server

How Are Indexes Created?

• Automatically : A unique index is created automatically when you define a PRIMARY KEY or UNIQUE constraint in a table definition.

• Manually : Users can create non unique indexes on columns to speed up access to the rows.

Creating an Index

• Create an index on one or more columns : 

         CREATE INDEX index ON table (column[, column]...);

• Improve the speed of query access to the 
• LAST_NAME column in the EMPLOYEES table :

         CREATE INDEX emp_last_name_idx ON employees(last_name);

Index Creation Guidelines :

Create an index when :

• A column contains a wide range of values
• A column contains a large number of null values
• One or more columns are frequently used together in a WHERE clause or a join condition
• The table is large and most queries are expected to retrieve less than 2% to 4% of the rows in the table

Do not create an index when :

• The columns are not often used as a condition in the query
• The table is small or most queries are expected to retrieve more than 2% to 4% of the rows in the table
• The table is updated frequently
• The indexed columns are referenced as part of an expression

Removing an Index

• Remove an index from the data dictionary by using the DROP INDEX command :

          DROP INDEX index;

• Remove the UPPER_LAST_NAME_IDX index 
• from the data dictionary :

          DROP INDEX emp_last_name_idx;