codekingpro/portable-devtools
115k
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.2. Structure of PL/pgSQL</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-overview.html" title="43.1. Overview" /><link rel="next" href="plpgsql-declarations.html" title="43.3. Declarations" /></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.2. Structure of <span class="application">PL/pgSQL</span></th></tr><tr><td width="10%" align="left"><a accesskey="p" href="plpgsql-overview.html" title="43.1. Overview">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-declarations.html" title="43.3. Declarations">Next</a></td></tr></table><hr /></div><div class="sect1" id="PLPGSQL-STRUCTURE"><div class="titlepage"><div><div><h2 class="title" style="clear: both">43.2. Structure of <span class="application">PL/pgSQL</span> <a href="#PLPGSQL-STRUCTURE" class="id_link">#</a></h2></div></div></div><p>3 Functions written in <span class="application">PL/pgSQL</span> are defined4 to the server by executing <a class="xref" href="sql-createfunction.html" title="CREATE FUNCTION"><span class="refentrytitle">CREATE FUNCTION</span></a> commands.5 Such a command would normally look like, say,6</p><pre class="programlisting">7CREATE FUNCTION somefunc(integer, text) RETURNS integer8AS '<em class="replaceable"><code>function body text</code></em>'9LANGUAGE plpgsql;10</pre><p>11 The function body is simply a string literal so far as <code class="command">CREATE12 FUNCTION</code> is concerned. It is often helpful to use dollar quoting13 (see <a class="xref" href="sql-syntax-lexical.html#SQL-SYNTAX-DOLLAR-QUOTING" title="4.1.2.4. Dollar-Quoted String Constants">Section 4.1.2.4</a>) to write the function14 body, rather than the normal single quote syntax. Without dollar quoting,15 any single quotes or backslashes in the function body must be escaped by16 doubling them. Almost all the examples in this chapter use dollar-quoted17 literals for their function bodies.18 </p><p>19 <span class="application">PL/pgSQL</span> is a block-structured language.20 The complete text of a function body must be a21 <em class="firstterm">block</em>. A block is defined as:22 23</p><pre class="synopsis">24[<span class="optional"> <<<em class="replaceable"><code>label</code></em>>> </span>]25[<span class="optional"> DECLARE26 <em class="replaceable"><code>declarations</code></em> </span>]27BEGIN28 <em class="replaceable"><code>statements</code></em>29END [<span class="optional"> <em class="replaceable"><code>label</code></em> </span>];30</pre><p>31 </p><p>32 Each declaration and each statement within a block is terminated33 by a semicolon. A block that appears within another block must34 have a semicolon after <code class="literal">END</code>, as shown above;35 however the final <code class="literal">END</code> that36 concludes a function body does not require a semicolon.37 </p><div class="tip"><h3 class="title">Tip</h3><p>38 A common mistake is to write a semicolon immediately after39 <code class="literal">BEGIN</code>. This is incorrect and will result in a syntax error.40 </p></div><p>41 A <em class="replaceable"><code>label</code></em> is only needed if you want to42 identify the block for use43 in an <code class="literal">EXIT</code> statement, or to qualify the names of the44 variables declared in the block. If a label is given after45 <code class="literal">END</code>, it must match the label at the block's beginning.46 </p><p>47 All key words are case-insensitive.48 Identifiers are implicitly converted to lower case49 unless double-quoted, just as they are in ordinary SQL commands.50 </p><p>51 Comments work the same way in <span class="application">PL/pgSQL</span> code as in52 ordinary SQL. A double dash (<code class="literal">--</code>) starts a comment53 that extends to the end of the line. A <code class="literal">/*</code> starts a54 block comment that extends to the matching occurrence of55 <code class="literal">*/</code>. Block comments nest.56 </p><p>57 Any statement in the statement section of a block58 can be a <em class="firstterm">subblock</em>. Subblocks can be used for59 logical grouping or to localize variables to a small group60 of statements. Variables declared in a subblock mask any61 similarly-named variables of outer blocks for the duration62 of the subblock; but you can access the outer variables anyway63 if you qualify their names with their block's label. For example:64</p><pre class="programlisting">65CREATE FUNCTION somefunc() RETURNS integer AS $$66<< outerblock >>67DECLARE68 quantity integer := 30;69BEGIN70 RAISE NOTICE 'Quantity here is %', quantity; -- Prints 3071 quantity := 50;72 --73 -- Create a subblock74 --75 DECLARE76 quantity integer := 80;77 BEGIN78 RAISE NOTICE 'Quantity here is %', quantity; -- Prints 8079 RAISE NOTICE 'Outer quantity here is %', outerblock.quantity; -- Prints 5080 END;81 82 RAISE NOTICE 'Quantity here is %', quantity; -- Prints 5083 84 RETURN quantity;85END;86$$ LANGUAGE plpgsql;87</pre><p>88 </p><div class="note"><h3 class="title">Note</h3><p>89 There is actually a hidden <span class="quote">“<span class="quote">outer block</span>”</span> surrounding the body90 of any <span class="application">PL/pgSQL</span> function. This block provides the91 declarations of the function's parameters (if any), as well as some92 special variables such as <code class="literal">FOUND</code> (see93 <a class="xref" href="plpgsql-statements.html#PLPGSQL-STATEMENTS-DIAGNOSTICS" title="43.5.5. Obtaining the Result Status">Section 43.5.5</a>). The outer block is94 labeled with the function's name, meaning that parameters and special95 variables can be qualified with the function's name.96 </p></div><p>97 It is important not to confuse the use of98 <code class="command">BEGIN</code>/<code class="command">END</code> for grouping statements in99 <span class="application">PL/pgSQL</span> with the similarly-named SQL commands100 for transaction101 control. <span class="application">PL/pgSQL</span>'s <code class="command">BEGIN</code>/<code class="command">END</code>102 are only for grouping; they do not start or end a transaction.103 See <a class="xref" href="plpgsql-transactions.html" title="43.8. Transaction Management">Section 43.8</a> for information on managing104 transactions in <span class="application">PL/pgSQL</span>.105 Also, a block containing an <code class="literal">EXCEPTION</code> clause effectively106 forms a subtransaction that can be rolled back without affecting the107 outer transaction. For more about that see <a class="xref" href="plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING" title="43.6.8. Trapping Errors">Section 43.6.8</a>.108 </p></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="plpgsql-overview.html" title="43.1. Overview">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-declarations.html" title="43.3. Declarations">Next</a></td></tr><tr><td width="40%" align="left" valign="top">43.1. Overview </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.3. Declarations</td></tr></table></div></body></html>