PostgreSQL-Stored Procedures

Stored Procedures

Stored Procedures are subroutines that are stored inside the database. They allow you to select and manipulate data, and, with the use of control structures and loops, perform complex computations and return the calculated result to the client. This saves considerable amounts of client/server communication.

PostgreSQL allows Stored Procedures to be written in several different Procedural Languages, including Perl, Python, TCL, and pgSQL - the PostgreSQL internal procedure language. User-defined Procedural Languages can also be used, and several of these languages are easily downloadable, e.g. PL/Java.

In this article, we will be using PL/pgSQL. PL/pgSQL is very similar to normal SQL, but adds many more features to it, like control structures and user-defined data types and functions.
Example 1: The Basic Stored Procedure

Let's get started with a very basic stored procedure that returns "Hello World!" - not very useful, I know, but it will get us started with the basic syntax of PL/pgSQL. Here's the code:

create or replace function hello() RETURNS text AS $$
DECLARE
hello text;
begin
hello := 'Hello World!';
return hello;
END;
$$ LANGUAGE plpgsql;

Here's what it does:

create or replace function hello() RETURNS text AS $$

Creates the function called hello which receives no parameters and returns text. You must always define what the function returns; use VOID if you don't need to return anything.

DECLARE

Opens the variable declarations block.

hello text;

Declares a variable called "hello" of type "text". To define multiple variables, use ";" as the separator. You can use any of standard types used in tables, like integer and float, and even user-defined types or domains.

BEGIN

Starts the actual function code.

hello := 'Hello World!';

Pupulates the variable "hello" with 'Hello World!'. Note that you have to use single quotes for string/text values.

return hello;

Returns our value.

END;

Ends the function.

$$ LANGUAGE plpgsql;

Defines what language we used - 'plpgsql' in this case. To call that function, you use the following SQL code.

select * from hello();

The output will be a text field called "hello", with the value of "Hello World!".
Example 2: Populating a Table with Test Data

This is another use of a Stored Procedure ('SP' from now on) - generating test data for your tables. Let's use last month's article as an example - we used a SP to generate 500K rows of data for one of our tables. Here's the code:

create or replace function test_data_computer()
RETURNS integer AS $$
DECLARE
count integer;
sql text;
begin
count = 1;
LOOP
sql = 'insert into computer(computer_id, computer_ram, cpu_id, video_id) values';
sql = sql || '('|| count ||', ' || random()*1024 || ', ' || (random()*49999)+1 || ', ' || (random()*49999)+1 || ')';
EXECUTE sql;
count = count + 1;
EXIT WHEN count > 500000;
END LOOP;
return count;
END;
$$ LANGUAGE plpgsql;

It starts much like our previous example, but this time we declare 2 variables instead of one. Things become different at line 8, where we introduce the LOOP statement. The loop is a basic repeating structure: it repeats the code inside indefinitely, until it finds a EXIT or EXIT WHEN clause.

Lines 9 and 10 are used to generate the SQL code to include a simple record in our tables. The double pipes ("||") is the concatenation operator. Random() generates a random float number between 0 and 1 (so "random()*49999)+1" will generate a random number between 1 and 50000).

Line 11 executes the SQL code stored inside the sql variable, adding the registry to the table.

Lines 12 and 13 are used to control the flow of the LOOP, and if omitted will make the loop an infinite one. "EXIT WHEN count > 500000;" makes the loop stop when the condition is met (when "count" goes over 500000 in this case.)

Line 14 closes the LOOP block, making the function go back to line 8, executing everything that is inside the loop again (and again, and again).

Line 15 returns the number of added registries (plus one in this case).
Example 3: Calculations and Date/Time Handling

Let's make up a scenario for this one. Imagine that you are building a system for a doctor, and one of the bits of data he wants is exactly how much time he spends with his patients (NOT just idling in the office.) Even more, he wants to be able to select the data for a given date or date interval, and he wants the option of selecting the records of either a single patient or all of them. Complex scenario, right? Well, we can solve it all with a single SP. These are the tables our database will have:

create table patient (
patient_id serial primary key,
patient_name text );

create table visits (
v_id serial primary key,
patient_id integer references patient,
v_date date,
v_time_start time,
v_time_end time );

One for the patients, another one to store the visits, with the date, start, and end time. Let's now populate the tables with some data:

insert into patient (patient_name) values ('Deividson');
insert into patient (patient_name) values ('John');
insert into patient (patient_name) values ('Benjamin');
insert into visits (patient_id, v_date, v_time_start, v_time_end) values (1, '10/04/2008', '08:00', '09:00');
insert into visits (patient_id, v_date, v_time_start, v_time_end) values (1, '14/04/2008', '13:00', '13:45');
insert into visits (patient_id, v_date, v_time_start, v_time_end) values (1, '18/04/2008', '10:00', '10:15');
insert into visits (patient_id, v_date, v_time_start, v_time_end) values (2, '11/04/2008', '14:00', '15:00');
insert into visits (patient_id, v_date, v_time_start, v_time_end) values (2, '12/04/2008', '14:00', '15:45');
insert into visits (patient_id, v_date, v_time_start, v_time_end) values (2, '17/04/2008', '14:00', '15:15');
insert into visits (patient_id, v_date, v_time_start, v_time_end) values (3, '15/04/2008', '08:00', '12:00');

Three patients, seven records - enough to test our SP. Here is the code:

CREATE OR REPLACE FUNCTION total( date1 date, date2 date, patient integer )
RETURNS interval AS $$
DECLARE
total interval;
rec record;
sql text;
BEGIN
total = '00:00:00'::time;
sql = 'select * from visits';

if date1 is not null OR patient is not null then
sql = sql || ' where ';
end if;

if patient is not null then
sql = sql || '(patient_id = ' || patient || ')';
end if;

if date2 is not null AND date1 is not null then
if patient is not null then
sql = sql || ' AND ';
end if;
sql = sql || '(v_date between ''' || date1 || ''' and ''' || date2 || ''')';
else
if date1 is not null then
if patient is not null then
sql = sql || ' AND ';
end if;
sql = sql || '(v_date = ''' || date1 || ''')';
end if;
end if;

for rec in EXECUTE sql loop
total = total + (rec.v_time_end - rec.v_time_start);
end loop;

return total;
END;
$$ LANGUAGE plpgsql;

Wow! Big one this time, eh? Let's take a look at it. The start of the code is pretty similar to the other examples, but we have 3 variables this time. 'total' will store the total time to return to the client, and 'rec' (of type record) is a variable that will hold the result of the query we will run.

On line 8, we start the variable total with the value of 00:00:00 - the "::" is a typecast - ":: time" means the string we're passing ("00:00:00") needs to be turned into a time.

From line 9 all the way down to line 31, all we are doing is creating the SQL statement that will select the data we want. Here, we use another type of structure - the IF. IFs are basic flow-control structures, and its syntax is (as in most programming languages):

IF (condition) THEN (commands) [ELSE (commands)] END IF;

The condition can be any logical comparison ( <, > , =, IS NULL, or IS NOT NULL), and you can combine multiple conditions using the logical operators (AND, OR, etc). If the condition is true, then the execution will continue with the commands inside the THEN clause, or if it's false, execution will move to the commands in the ELSE (if it exists), or to after the END IF.

It's in those IFs that we create the conditions (single date, date interval, single patient, etc.)

On line 33, we execute the SQL code we generated, looping over each of the records of the result. We then add the duration of each visit in the 'total' variable, and return the result when there are no more records available.

We can call this SP in one of several different ways, each way selecting a different data set and giving us a different result:

# All the records, from all patients
select * from total(NULL, NULL, NULL);

# All the records, from patient #3 only
select * from total(NULL, NULL, 3);

# Records from '14/04/2008', all patients
select * from total('14/04/2008', NULL, NULL);

# Records from '14/04/2008', patient #1 only
select * from total('14/04/2008', NULL, 1);

# Records from '14/04/2008' through '17/04/2008', all patients
select * from total('14/04/2008', '17/04/2008', NULL);

# Records from '14/04/2008' through '17/04/2008', patient #2 only.
select * from total('14/04/2008', '17/04/2008', 2);

Conclusion

Stored Procedures are powerful and flexible, and can be a very good way to help you pre-select and pre-process data, as well as allowing you to manipulate data and run code directly on the server.

PostgreSQL offers a comprehensive manual on their site, including a chapter about PL/pgSQL. You can find it here: http://www.postgresql.org/docs/8.3/static/plpgsql.html

That's it for Stored Procedures - see you next month, when we'll discuss Triggers!

Postgresql Interview Questions

1) Explain what is PostgreSQL?
This is regarded as one of the most successful open source database in the world. This is also used to create advanced applications. This relies on Object relational database management system. Familiarity with UNIX and Linux can be an added advantage while using PostgreSQL.

2) State some of the advanced features of PostgreSQL?
These are the following features which are present in PostgreSQL they are
1) Object relational database
2) Extensibility and support for SQL
3) Database validation and flexible API
4) Procedural languages and MVCC
5) Client server and WAL.

3) Explain about Write Ahead logging?
This feature increases the reliability of the database by logging changes before any changes or updations to the data base. This provides log of database incase of a database crash. This helps to start the work from the point it was discontinued.

4) Explain about Multi version concurrency control?
Multi version concurrency control or MVCC is used to avoid unnecessary locking of the database. This removes the time lag for the user to log into his database. This feature or time lag occurs when some one else is on the content. All the transactions are kept as a record.

5) What are the languages which PostgreSQL supports?
Some of the languages which PostgreSQL supports are as follows: -
It supports a language of its own known as PL/pgSQL and it supports internal procedural languages. Pl/pgSQL can be compared to oracle, PL/SQL, etc. Languages such as Perl, Python, TCL can be used as embedded languages.

6) Explain about the command enable debug?
This command is used for enabling compilation of all libraries and applications. This process generally slows down the system and it also increases the binary file size. Debugging symbols are present which can assist developers in noticing bugs and problems associated with their script.

7) Explain about functions in PostgreSQL?
Functions are important because they help the code to be executed on the server. Some of the languages which can program functions for efficient use are PL/pgSQL which is the native language of PostgreSQL. Scripting languages are supported by many languages such as PHP, Perl, Python, etc. PL/R a statistical language can also be used.

8) Explain about indices of PostgreSQL?
There are built in functions such as B-tree, hash table, and GIST indices can be used or users can define their own indices. PostgreSQL can scan the index backwards. Expression index could be created with the result of an expression. Partial index created with addition of WHERE clause.

9) Explain about Triggers?
By SQL query you can trigger an event. Triggers can be activated with the help of INSERT and UPDATE queries. These can be attached to tables. Triggers more than one can be triggered alphabetically. These triggers have the capability to invoke functions from other languages.

10) What are the different data types supported by PostgreSQL?
There are different data types which are supported they are: -
1) Arbitrary precision numeric’s
2) Geometric primitives
3) Arrays
4) XML etc
Users can create their own indexes and make them indexed.

11) Explain about database administration tools?
There are various data administration tools they are
1) Psql
2) Pgadmin
3) Phppgadmin
Most of these tools are front end administration tools and web based interfaces. Out of these phppgadmin is the most popular one.

12) Explain about pgadmin?
Pgadmin forms a graphical front end administration tool. This feature is available under free software released under Artistic License. Pgadmin iii is the new database administration tool released under artistic license.

13) How do you create a data base with postgreSQL?
Creating a database is the primary step in creating a database. A command
$createdb newdatabasedb
CREATE DATABASE
This creates a new database and a message displays CREATE DATABASE which indicates that the creation of the database was successful.

14) What are the various enhancements to the straight relational data model by PostgreSQL?
There are various enhancements provided to the straight relational data model by postgre SQl they are support for arrays which includes multiple values, inheritance, functions and extensibility. Jargon differs because of its object oriented nature where tables are called as classes.

15) Explain about tokens?
Tokens are also known to contain several special character symbols. It can be considered as keyword, constant, identifier and quoted identifier. Keywords include pre defined SQL meanings and SQL commands. Variable names such as tables, columns, etc are represented by identifiers.

16) Explain about string constants?
String constant contains a sequence of characters bound by single quotes. This feature is used during insertion of a character or passing character to database objects. PostgreSQL allows the usage of single quotes but embedded by a C style backslash. This feature is important in parsing data.

17) Explain about concurreny with the help of MVCC?
Multi version concurrency control is used to manage concurrency. This feature is very useful because changes made in the database will not be visible to other users until the transaction is completed. This removes the need for read locks. ACID principles are given a further boost by this feature and can be implemented in general.