Once you have decided on a procedural language, it is time to get started with real work. The first important fact you have to keep in mind is that PostgreSQL relies heavily on an object-oriented feature called function overloading. This term means that various functions can have the same name but accept different input parameters.
One function may be called func(int, int), while some other function may be called func(text, text). Always keep in mind that the entire signature of the function is what counts:
test=# CREATE FUNCTION mysum(int, int) RETURNS int AS ' SELECT $1 + $2 ' LANGUAGE 'sql'; CREATE FUNCTION test=# CREATE FUNCTION mysum(text, text) RETURNS text AS ' SELECT $1 || $2 ' LANGUAGE 'sql'; CREATE FUNCTION
In this case, two functions have been defined. The first will take two integers, and the second takes two text values. Depending on how the function is called, different portions of code will be executed:
test=# SELECT mysum(10, 20);
mysum
-------
30
(1 row)The behavior of the function changes as the input data types change:
test=# SELECT mysum('10', '20'),
mysum
-------
1020
(1 row)You are advised to be very careful here.
Let's discuss a very common problem many people all over the world face. Experience has shown that many people commit their code way too early. Consider the following flow:
- Somebody created
func(int, int). - At some point, the programmer finds out that actually,
func(int8, int8)is needed. - The new function is accidentally created alongside the old function.
- Nobody notices that there are two functions around until it is too late.
This is not a theoretical edge case. It happens all the time all over the world. A mistake like this can cost people dearly.
One way to reduce the odds of such a mistake is the use of transactions. Remember that in PostgreSQL, transactions can even be used along with DDLs (CREATE FUNCTION, ALTER TABLE, and so on). By (simply) not committing a change while you are working on the code, you can prevent side effects, such as leftovers, and this helps to avoid the problem of cleaning up things during and after development. Writing database code is not like writing code in a programming language. If you remove a function from the code, it is gone. In a database, removing a function from a text file somewhere does not necessarily remove the function from the database system. BEGIN, COMMIT, or ROLLBACK can help to reduce the pain.
The module_1.sql and module_2.sql files contain all of the code. Note that there is no COMMIT statement. It is only added after all the modules are somewhat ready for the rest of the world. The i function is a very convenient tool used to include modules and load them as parts of one transaction. To those of you who are using the normal Linux command line to develop software, this might be very helpful.
So far, you have seen how transactions and function creation work, but there are other aspects here as well. One important thing is that inside a function, the programmer cannot start or end transactions; this makes sense. Consider the following command:
SELECT * FROM func();
Let's assume func() returns 1 million rows. There is no way to open a transaction after returning 200,000 rows or so. The same applies to commit. You cannot just commit at any place you want inside an operation.
The golden rule is that a function is always a part of a transaction. It never starts or ends transactions. A function always operates within the scope of the statement running it.
However, there is one thing you can take advantage of: a PL/pgSQL function can use so-called exceptions. Practically, an exception can help you to achieve the same thing as with a savepoint. Here is an example:
CREATE FUNCTION failure(int) RETURNS int AS $$ DECLARE v_one ALIAS FOR $1; BEGIN RETURN v_one / 0; EXCEPTION WHEN division_by_zero THEN RETURN 0; END; $$ LANGUAGE 'plpgsql';
This procedure will definitely cause an error. As you have seen in the previous chapters, an error actually leads to a failing transaction. However, the exception helps us catch the error and ensures that the transaction can be recovered.