Database Reference
In-Depth Information
Why should you use PL/pgSQL?
SQL statements are executed individually, so your query is sent one by one to the
server to be parsed, processed, and returned as the result. Doing this occasionally is
not an issue but when you have to execute thousands of queries each day, you can
simply write a PL/pgSQL function. More speciically, this is a stored procedure that
can perform all SQL instructions using any of the
loop
control structures available.
Loop control structures such as
WHILE
and
FOR
, will be executed on the decision of
various conditional structures used along with the variables that are declared to store
the results and even process these results later. Additionally, it can handle
errors
and
exceptions
as well.
PL/pgSQL encapsulates an expression style of SQL and implements programming
concepts such as other languages.
You can write these functions in a variety of available languages, which for example
can be Perl, Python, Java, and so on. Functions written in PL/pgSQL can invoke
other functions or contain sub-blocks inside the main block.
With this much extensibility, it does not need to be mentioned that performance will
certainly be improved.
Installing PL/pgSQL
You should make sure that you have installed the procedural language you are about
to use. PostgreSQL supports many procedural languages, and it ensured that the
installation process became seamless.
You can consult the PostgreSQL manual present online at
http://www.
postgresql.org/docs/9.4/static/index.html
for general syntax
of statements, declarations, functions, and so on. In fact, this will also
serve as a source for an in-detail study of PostgreSQL features and usage.
Simply launch the command-line utility and execute the following command:
$ createlang plpgsql database-name
PL/pgSQL is installed by default and executing the preceding
command might result in telling you that the language is already
installed.
