PL/pgSQL is a loadable procedural language for the Postgres database system.
This package was originally written by Jan Wieck. This documentation was in part written by Roberto Mello (<[email protected]>).
The design goals of PL/pgSQL were to create a loadable procedural language that
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.
The PL/pgSQL call handler parses the function's source text and produces an internal binary instruction tree the first time the function is called. The produced bytecode is identified in the call handler by the object ID of the function. This ensures that changing a function by a DROP/CREATE sequence will take effect without establishing a new database connection.
For all expressions and SQL statements used in the function, the PL/pgSQL bytecode interpreter creates a prepared execution plan using the SPI manager's SPI_prepare() and SPI_saveplan() functions. This is done the first time the individual statement is processed in the PL/pgSQL function. Thus, a function with conditional code that contains many statements for which execution plans would be required, will only prepare and save those plans that are really used during the lifetime of the database connection.
This means that you have to be careful about your user-defined functions. For example:
CREATE FUNCTION populate() RETURNS INTEGER AS '
DECLARE
-- Declarations
BEGIN
PERFORM my_function();
END;
' LANGUAGE 'plpgsql';
If you create the above function, it will reference the OID
for my_function() in its bytecode. Later,
if you drop and re-create my_function(),
then populate() will not be able to find
my_function() anymore. You would then
have to re-create populate().
Because PL/pgSQL saves execution plans in this way, queries that appear directly in a PL/pgSQL function must refer to the same tables and fields on every execution; that is, you cannot use a parameter as the name of a table or field in a query. To get around this restriction, you can construct dynamic queries using the PL/pgSQL EXECUTE statement --- at the price of constructing a new query plan on every execution.
Except for input/output conversion and calculation functions for user defined types, anything that can be defined in C language functions can also be done with PL/pgSQL. It is possible to create complex conditional computation functions and later use them to define operators or use them in functional indices.
Better performance (see Section 24.1.1.1)
SQL support (see Section 24.1.1.2)
Portability (see Section 24.1.1.3)
SQL is the language PostgreSQL (and most other Relational Databases) use as query language. It's portable and easy to learn. But every SQL statement must be executed individually by the database server.
That means that your client application must send each query to the database server, wait for it to process it, receive the results, do some computation, then send other queries to the server. All this incurs inter process communication and may also incur network overhead if your client is on a different machine than the database server.
With PL/pgSQL you can group a block of computation and a series of queries inside the database server, thus having the power of a procedural language and the ease of use of SQL, but saving lots of time because you don't have the whole client/server communication overhead. Your application will enjoy a considerable performance increase by using PL/pgSQL.
PL/pgSQL adds the power of a procedural language to the flexibility and ease of SQL. With PL/pgSQL you can use all the datatypes, columns, operators and functions of SQL.
Because PL/pgSQL functions run inside PostgreSQL, these functions will run on any platform where PostgreSQL runs. Thus you can reuse code and have less development costs.
Developing in PL/pgSQL is pretty straight forward, especially if you have developed in other database procedural languages, such as Oracle's PL/SQL. Two good ways of developing in PL/pgSQL are:
Using a text editor and reloading the file with psql
Using PostgreSQL's GUI Tool: pgaccess
One good way to develop in PL/pgSQL is to simply use the text editor of your choice to create your functions, and in another console, use psql (PostgreSQL's interactive monitor) to load those functions. If you are doing it this way (and if you are a PL/pgSQL novice or in debugging stage), it is a good idea to always DROP your function before creating it. That way when you reload the file, it'll drop your functions and then re-create them. For example:
drop function testfunc(integer);
create function testfunc(integer) return integer as '
....
end;
' language 'plpgsql';
When you load the file for the first time, PostgreSQL will raise a warning saying this function doesn't exist and go on to create it. To load an SQL file (filename.sql) into a database named "dbname", use the command:
psql -f filename.sql dbname
Another good way to develop in PL/pgSQL is using PostgreSQL's GUI tool: pgaccess. It does some nice things for you, like escaping single-quotes, and making it easy to recreate and debug functions.