codekingpro/portable-devtools

1<?xml version="1.0" encoding="UTF-8" standalone="no"?>2<!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 16.3 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 <a href="#PLPGSQL-OVERVIEW" class="id_link">#</a></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>3  <span class="application">PL/pgSQL</span> is a loadable procedural4  language for the <span class="productname">PostgreSQL</span> database5  system.  The design goals of <span class="application">PL/pgSQL</span> were to create6  a loadable procedural language that7 8    </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>9       can be used to create functions, procedures, and triggers,10      </p></li><li class="listitem"><p>11       adds control structures to the <acronym class="acronym">SQL</acronym> language,12      </p></li><li class="listitem"><p>13       can perform complex computations,14      </p></li><li class="listitem"><p>15       inherits all user-defined types, functions, procedures, and operators,16      </p></li><li class="listitem"><p>17       can be defined to be trusted by the server,18      </p></li><li class="listitem"><p>19       is easy to use.20      </p></li></ul></div><p>21   </p><p>22    Functions created with <span class="application">PL/pgSQL</span> can be23    used anywhere that built-in functions could be used.24    For example, it is possible to25    create complex conditional computation functions and later use26    them to define operators or use them in index expressions.27   </p><p>28    In <span class="productname">PostgreSQL</span> 9.0 and later,29    <span class="application">PL/pgSQL</span> is installed by default.30    However it is still a loadable module, so especially security-conscious31    administrators could choose to remove it.32   </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> <a href="#PLPGSQL-ADVANTAGES" class="id_link">#</a></h3></div></div></div><p>33     <acronym class="acronym">SQL</acronym> is the language <span class="productname">PostgreSQL</span>34     and most other relational databases use as query language. It's35     portable and easy to learn. But every <acronym class="acronym">SQL</acronym>36     statement must be executed individually by the database server.37    </p><p>38     That means that your client application must send each query to39     the database server, wait for it to be processed, receive and40     process the results, do some computation, then send further41     queries to the server.  All this incurs interprocess42     communication and will also incur network overhead if your client43     is on a different machine than the database server.44    </p><p>45     With <span class="application">PL/pgSQL</span> you can group a block of46     computation and a series of queries <span class="emphasis"><em>inside</em></span>47     the database server, thus having the power of a procedural48     language and the ease of use of SQL, but with considerable49     savings of client/server communication overhead.50    </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p> Extra round trips between51     client and server are eliminated </p></li><li class="listitem"><p> Intermediate results that the client does not52     need do not have to be marshaled or transferred between server53     and client </p></li><li class="listitem"><p> Multiple rounds of query54     parsing can be avoided </p></li></ul></div><p> This can result in a considerable performance increase as55    compared to an application that does not use stored functions.56    </p><p>57     Also, with <span class="application">PL/pgSQL</span> you can use all58     the data types, operators and functions of SQL.59    </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 <a href="#PLPGSQL-ARGS-RESULTS" class="id_link">#</a></h3></div></div></div><p>60     Functions written in <span class="application">PL/pgSQL</span> can accept61     as arguments any scalar or array data type supported by the server,62     and they can return a result of any of these types.  They can also63     accept or return any composite type (row type) specified by name.64     It is also possible to declare a <span class="application">PL/pgSQL</span>65     function as accepting <code class="type">record</code>, which means that any66     composite type will do as input, or67     as returning <code class="type">record</code>, which means that the result68     is a row type whose columns are determined by specification in the69     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>.70    </p><p>71     <span class="application">PL/pgSQL</span> functions can be declared to accept a variable72     number of arguments by using the <code class="literal">VARIADIC</code> marker.  This73     works exactly the same way as for SQL functions, as discussed in74     <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>.75    </p><p>76     <span class="application">PL/pgSQL</span> functions can also be declared to77     accept and return the polymorphic types described in78     <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 data79     types handled by the function to vary from call to call.80     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>.81    </p><p>82     <span class="application">PL/pgSQL</span> functions can also be declared to return83     a <span class="quote">“<span class="quote">set</span>”</span> (or table) of any data type that can be returned as84     a single instance.  Such a function generates its output by executing85     <code class="command">RETURN NEXT</code> for each desired element of the result86     set, or by using <code class="command">RETURN QUERY</code> to output the result of87     evaluating a query.88    </p><p>89     Finally, a <span class="application">PL/pgSQL</span> function can be declared to return90     <code class="type">void</code> if it has no useful return value.  (Alternatively, it91     could be written as a procedure in that case.)92    </p><p>93     <span class="application">PL/pgSQL</span> functions can also be declared with output94     parameters in place of an explicit specification of the return type.95     This does not add any fundamental capability to the language, but96     it is often convenient, especially for returning multiple values.97     The <code class="literal">RETURNS TABLE</code> notation can also be used in place98     of <code class="literal">RETURNS SETOF</code>.99    </p><p>100     Specific examples appear in101     <a class="xref" href="plpgsql-declarations.html#PLPGSQL-DECLARATION-PARAMETERS" title="43.3.1. Declaring Function Parameters">Section 43.3.1</a> and102     <a class="xref" href="plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RETURNING" title="43.6.1. Returning from a Function">Section 43.6.1</a>.103    </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 16.3 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>