19.1. Overview

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 (within any one backend process). The instruction tree fully translates the PL/pgSQL statement structure, but individual SQL expressions and SQL queries used in the function are not translated immediately.

As each expression and SQL query is first used in the function, the PL/pgSQL interpreter creates a prepared execution plan (using the SPI manager's SPI_prepare and SPI_saveplan functions). Subsequent visits to that expression or query re-use the prepared plan. Thus, a function with conditional code that contains many statements for which execution plans might be required will only prepare and save those plans that are really used during the lifetime of the database connection. This can substantially reduce the total amount of time required to parse, and generate query plans for the statements in a procedural language function. A disadvantage is that errors in a specific expression or query may not be detected until that part of the function is reached in execution.

Once PL/pgSQL has made a query plan for a particular query in a function, it will re-use that plan for the life of the database connection. This is usually a win for performance, but it can cause some problems if you dynamically alter your database schema. For example:

CREATE FUNCTION populate() RETURNS INTEGER AS '
DECLARE
    -- Declarations
BEGIN
    PERFORM my_function();
END;
' LANGUAGE 'plpgsql';

If you execute the above function, it will reference the OID for my_function() in the query plan produced for the PERFORM statement. 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(), or at least start a new database session so that it will be compiled afresh.

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 indexes.

CREATE OR REPLACE FUNCTION testfunc(INTEGER) RETURNS INTEGER AS '
	  ....
end;
' LANGUAGE 'plpgsql';

    \i filename.sql