summaryrefslogtreecommitdiffstats
path: root/doc/src/sgml/html/plpgsql-overview.html
blob: a875ea109d7a1d9a17b62c921cad3781eda96e95 (plain)
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.7 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.7 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>