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.
For example, it is possible to
create complex conditional computation functions and later use
them to define operators or use them in index expressions.
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 each session). The instruction tree
fully translates the
PL/pgSQL statement structure, but individual
SQL expressions and SQL commands
used in the function are not translated immediately.
As each expression and SQL command 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 command
reuse 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 execution plans for the statements in a
PL/pgSQL function. A disadvantage is that errors
in a specific expression or command may not be detected until that
part of the function is reached in execution.
Once PL/pgSQL has made an execution plan for a particular
command in a function, it will reuse 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 execution plan produced for
the PERFORM statement. Later, if you
drop and recreate my_function(), then
populate() will not be able to find
my_function() anymore. You would then have to
recreate populate(), or at least start a new
database session so that it will be compiled afresh. Another way
to avoid this problem is to use CREATE OR REPLACE
FUNCTION when updating the definition of
my_function (when a function is
"replaced", its OID is not changed).
Because PL/pgSQL saves execution plans
in this way, SQL commands that appear directly in a
PL/pgSQL function must refer to the
same tables and columns on every execution; that is, you cannot use
a parameter as the name of a table or column in an SQL command. To get
around this restriction, you can construct dynamic commands using
the PL/pgSQL EXECUTE
statement — at the price of constructing a new execution plan on
every execution.
Note: The PL/pgSQL
EXECUTE statement is not related to the
EXECUTE SQL
statement supported by the
PostgreSQL server. The server's
EXECUTE statement cannot be used within
PL/pgSQL functions (and is not needed).
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 be processed, receive the
results, do some computation, then send other queries to the
server. All this incurs interprocess 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. This can make for a
considerable performance increase.
Also, with PL/pgSQL you can use all
the data types, operators and functions of SQL.
Functions written in PL/pgSQL can accept
as arguments any scalar or array data type supported by the server,
and they can return a result of any of these types. They can also
accept or return any composite type (row type) specified by name.
It is also possible to declare a PL/pgSQL
function as returning record, which means that the result
is a row type whose columns are determined by specification in the
calling query, as discussed in Section 7.2.1.4.
PL/pgSQL functions may also be declared to accept
and return the polymorphic types
anyelement and anyarray. The actual
data types handled by a polymorphic function can vary from call to
call, as discussed in Section 32.2.5.
An example is shown in Section 36.4.1.
PL/pgSQL functions can also be declared to return
a "set", or table, of any data type they can return a single
instance of. Such a function generates its output by executing
RETURN NEXT for each desired element of the result set.
Finally, a PL/pgSQL function may be declared to return
void if it has no useful return value.
Note: PL/pgSQL does not currently have full support for
domain types: it treats a domain the same as the underlying scalar
type. This means that constraints associated with the domain will
not be enforced. This is not an issue for function arguments, but
it is a hazard if you declare a PL/pgSQL function
as returning a domain type.
PL/pgSQL functions can also be declared with output
parameters in place of an explicit specification of the return type.
This does not add any fundamental capability to the language, but
it is often convenient, especially for returning multiple values.
Specific examples appear in
Section 36.4.1 and
Section 36.7.1.
