1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
|
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>43.1. Overview</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="plpgsql.html" title="Chapter 43. PL/pgSQL — SQL Procedural Language" /><link rel="next" href="plpgsql-structure.html" title="43.2. Structure of PL/pgSQL" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">43.1. Overview</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="plpgsql.html" title="Chapter 43. PL/pgSQL — SQL Procedural Language">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="plpgsql.html" title="Chapter 43. PL/pgSQL — SQL Procedural Language">Up</a></td><th width="60%" align="center">Chapter 43. <span class="application">PL/pgSQL</span> — <acronym class="acronym">SQL</acronym> Procedural Language</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 15.4 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="plpgsql-structure.html" title="43.2. Structure of PL/pgSQL">Next</a></td></tr></table><hr /></div><div class="sect1" id="PLPGSQL-OVERVIEW"><div class="titlepage"><div><div><h2 class="title" style="clear: both">43.1. Overview</h2></div></div></div><div class="toc"><dl class="toc"><dt><span class="sect2"><a href="plpgsql-overview.html#PLPGSQL-ADVANTAGES">43.1.1. Advantages of Using <span class="application">PL/pgSQL</span></a></span></dt><dt><span class="sect2"><a href="plpgsql-overview.html#PLPGSQL-ARGS-RESULTS">43.1.2. Supported Argument and Result Data Types</a></span></dt></dl></div><p>
<span class="application">PL/pgSQL</span> is a loadable procedural
language for the <span class="productname">PostgreSQL</span> database
system. The design goals of <span class="application">PL/pgSQL</span> were to create
a loadable procedural language that
</p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
can be used to create functions, procedures, and triggers,
</p></li><li class="listitem"><p>
adds control structures to the <acronym class="acronym">SQL</acronym> language,
</p></li><li class="listitem"><p>
can perform complex computations,
</p></li><li class="listitem"><p>
inherits all user-defined types, functions, procedures, and operators,
</p></li><li class="listitem"><p>
can be defined to be trusted by the server,
</p></li><li class="listitem"><p>
is easy to use.
</p></li></ul></div><p>
</p><p>
Functions created with <span class="application">PL/pgSQL</span> can be
used anywhere that built-in functions could be used.
For example, it is possible to
create complex conditional computation functions and later use
them to define operators or use them in index expressions.
</p><p>
In <span class="productname">PostgreSQL</span> 9.0 and later,
<span class="application">PL/pgSQL</span> is installed by default.
However it is still a loadable module, so especially security-conscious
administrators could choose to remove it.
</p><div class="sect2" id="PLPGSQL-ADVANTAGES"><div class="titlepage"><div><div><h3 class="title">43.1.1. Advantages of Using <span class="application">PL/pgSQL</span></h3></div></div></div><p>
<acronym class="acronym">SQL</acronym> is the language <span class="productname">PostgreSQL</span>
and most other relational databases use as query language. It's
portable and easy to learn. But every <acronym class="acronym">SQL</acronym>
statement must be executed individually by the database server.
</p><p>
That means that your client application must send each query to
the database server, wait for it to be processed, receive and
process the results, do some computation, then send further
queries to the server. All this incurs interprocess
communication and will also incur network overhead if your client
is on a different machine than the database server.
</p><p>
With <span class="application">PL/pgSQL</span> you can group a block of
computation and a series of queries <span class="emphasis"><em>inside</em></span>
the database server, thus having the power of a procedural
language and the ease of use of SQL, but with considerable
savings of client/server communication overhead.
</p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p> Extra round trips between
client and server are eliminated </p></li><li class="listitem"><p> Intermediate results that the client does not
need do not have to be marshaled or transferred between server
and client </p></li><li class="listitem"><p> Multiple rounds of query
parsing can be avoided </p></li></ul></div><p> This can result in a considerable performance increase as
compared to an application that does not use stored functions.
</p><p>
Also, with <span class="application">PL/pgSQL</span> you can use all
the data types, operators and functions of SQL.
</p></div><div class="sect2" id="PLPGSQL-ARGS-RESULTS"><div class="titlepage"><div><div><h3 class="title">43.1.2. Supported Argument and Result Data Types</h3></div></div></div><p>
Functions written in <span class="application">PL/pgSQL</span> 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 <span class="application">PL/pgSQL</span>
function as accepting <code class="type">record</code>, which means that any
composite type will do as input, or
as returning <code class="type">record</code>, which means that the result
is a row type whose columns are determined by specification in the
calling query, as discussed in <a class="xref" href="queries-table-expressions.html#QUERIES-TABLEFUNCTIONS" title="7.2.1.4. Table Functions">Section 7.2.1.4</a>.
</p><p>
<span class="application">PL/pgSQL</span> functions can be declared to accept a variable
number of arguments by using the <code class="literal">VARIADIC</code> marker. This
works exactly the same way as for SQL functions, as discussed in
<a class="xref" href="xfunc-sql.html#XFUNC-SQL-VARIADIC-FUNCTIONS" title="38.5.6. SQL Functions with Variable Numbers of Arguments">Section 38.5.6</a>.
</p><p>
<span class="application">PL/pgSQL</span> functions can also be declared to
accept and return the polymorphic types described in
<a class="xref" href="extend-type-system.html#EXTEND-TYPES-POLYMORPHIC" title="38.2.5. Polymorphic Types">Section 38.2.5</a>, thus allowing the actual data
types handled by the function to vary from call to call.
Examples appear in <a class="xref" href="plpgsql-declarations.html#PLPGSQL-DECLARATION-PARAMETERS" title="43.3.1. Declaring Function Parameters">Section 43.3.1</a>.
</p><p>
<span class="application">PL/pgSQL</span> functions can also be declared to return
a <span class="quote">“<span class="quote">set</span>”</span> (or table) of any data type that can be returned as
a single instance. Such a function generates its output by executing
<code class="command">RETURN NEXT</code> for each desired element of the result
set, or by using <code class="command">RETURN QUERY</code> to output the result of
evaluating a query.
</p><p>
Finally, a <span class="application">PL/pgSQL</span> function can be declared to return
<code class="type">void</code> if it has no useful return value. (Alternatively, it
could be written as a procedure in that case.)
</p><p>
<span class="application">PL/pgSQL</span> 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.
The <code class="literal">RETURNS TABLE</code> notation can also be used in place
of <code class="literal">RETURNS SETOF</code>.
</p><p>
Specific examples appear in
<a class="xref" href="plpgsql-declarations.html#PLPGSQL-DECLARATION-PARAMETERS" title="43.3.1. Declaring Function Parameters">Section 43.3.1</a> and
<a class="xref" href="plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RETURNING" title="43.6.1. Returning from a Function">Section 43.6.1</a>.
</p></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="plpgsql.html" title="Chapter 43. PL/pgSQL — SQL Procedural Language">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="plpgsql.html" title="Chapter 43. PL/pgSQL — SQL Procedural Language">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="plpgsql-structure.html" title="43.2. Structure of PL/pgSQL">Next</a></td></tr><tr><td width="40%" align="left" valign="top">Chapter 43. <span class="application">PL/pgSQL</span> — <acronym class="acronym">SQL</acronym> Procedural Language </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 15.4 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 43.2. Structure of <span class="application">PL/pgSQL</span></td></tr></table></div></body></html>
|
