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.3. Declarations</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-structure.html" title="43.2. Structure of PL/pgSQL" /><link rel="next" href="plpgsql-expressions.html" title="43.4. Expressions" /></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.3. Declarations</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="plpgsql-structure.html" title="43.2. Structure of PL/pgSQL">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-expressions.html" title="43.4. Expressions">Next</a></td></tr></table><hr /></div><div class="sect1" id="PLPGSQL-DECLARATIONS"><div class="titlepage"><div><div><h2 class="title" style="clear: both">43.3. Declarations <a href="#PLPGSQL-DECLARATIONS" class="id_link">#</a></h2></div></div></div><div class="toc"><dl class="toc"><dt><span class="sect2"><a href="plpgsql-declarations.html#PLPGSQL-DECLARATION-PARAMETERS">43.3.1. Declaring Function Parameters</a></span></dt><dt><span class="sect2"><a href="plpgsql-declarations.html#PLPGSQL-DECLARATION-ALIAS">43.3.2. <code class="literal">ALIAS</code></a></span></dt><dt><span class="sect2"><a href="plpgsql-declarations.html#PLPGSQL-DECLARATION-TYPE">43.3.3. Copying Types</a></span></dt><dt><span class="sect2"><a href="plpgsql-declarations.html#PLPGSQL-DECLARATION-ROWTYPES">43.3.4. Row Types</a></span></dt><dt><span class="sect2"><a href="plpgsql-declarations.html#PLPGSQL-DECLARATION-RECORDS">43.3.5. Record Types</a></span></dt><dt><span class="sect2"><a href="plpgsql-declarations.html#PLPGSQL-DECLARATION-COLLATION">43.3.6. Collation of <span class="application">PL/pgSQL</span> Variables</a></span></dt></dl></div><p>3 All variables used in a block must be declared in the4 declarations section of the block.5 (The only exceptions are that the loop variable of a <code class="literal">FOR</code> loop6 iterating over a range of integer values is automatically declared as an7 integer variable, and likewise the loop variable of a <code class="literal">FOR</code> loop8 iterating over a cursor's result is automatically declared as a9 record variable.)10 </p><p>11 <span class="application">PL/pgSQL</span> variables can have any SQL data type, such as12 <code class="type">integer</code>, <code class="type">varchar</code>, and13 <code class="type">char</code>.14 </p><p>15 Here are some examples of variable declarations:16</p><pre class="programlisting">17user_id integer;18quantity numeric(5);19url varchar;20myrow tablename%ROWTYPE;21myfield tablename.columnname%TYPE;22arow RECORD;23</pre><p>24 </p><p>25 The general syntax of a variable declaration is:26</p><pre class="synopsis">27<em class="replaceable"><code>name</code></em> [<span class="optional"> CONSTANT </span>] <em class="replaceable"><code>type</code></em> [<span class="optional"> COLLATE <em class="replaceable"><code>collation_name</code></em> </span>] [<span class="optional"> NOT NULL </span>] [<span class="optional"> { DEFAULT | := | = } <em class="replaceable"><code>expression</code></em> </span>];28</pre><p>29 The <code class="literal">DEFAULT</code> clause, if given, specifies the initial value assigned30 to the variable when the block is entered. If the <code class="literal">DEFAULT</code> clause31 is not given then the variable is initialized to the32 <acronym class="acronym">SQL</acronym> null value.33 The <code class="literal">CONSTANT</code> option prevents the variable from being34 assigned to after initialization, so that its value will remain constant35 for the duration of the block.36 The <code class="literal">COLLATE</code> option specifies a collation to use for the37 variable (see <a class="xref" href="plpgsql-declarations.html#PLPGSQL-DECLARATION-COLLATION" title="43.3.6. Collation of PL/pgSQL Variables">Section 43.3.6</a>).38 If <code class="literal">NOT NULL</code>39 is specified, an assignment of a null value results in a run-time40 error. All variables declared as <code class="literal">NOT NULL</code>41 must have a nonnull default value specified.42 Equal (<code class="literal">=</code>) can be used instead of PL/SQL-compliant43 <code class="literal">:=</code>.44 </p><p>45 A variable's default value is evaluated and assigned to the variable46 each time the block is entered (not just once per function call).47 So, for example, assigning <code class="literal">now()</code> to a variable of type48 <code class="type">timestamp</code> causes the variable to have the49 time of the current function call, not the time when the function was50 precompiled.51 </p><p>52 Examples:53</p><pre class="programlisting">54quantity integer DEFAULT 32;55url varchar := 'http://mysite.com';56transaction_time CONSTANT timestamp with time zone := now();57</pre><p>58 </p><p>59 Once declared, a variable's value can be used in later initialization60 expressions in the same block, for example:61</p><pre class="programlisting">62DECLARE63 x integer := 1;64 y integer := x + 1;65</pre><p>66 </p><div class="sect2" id="PLPGSQL-DECLARATION-PARAMETERS"><div class="titlepage"><div><div><h3 class="title">43.3.1. Declaring Function Parameters <a href="#PLPGSQL-DECLARATION-PARAMETERS" class="id_link">#</a></h3></div></div></div><p>67 Parameters passed to functions are named with the identifiers68 <code class="literal">$1</code>, <code class="literal">$2</code>,69 etc. Optionally, aliases can be declared for70 <code class="literal">$<em class="replaceable"><code>n</code></em></code>71 parameter names for increased readability. Either the alias or the72 numeric identifier can then be used to refer to the parameter value.73 </p><p>74 There are two ways to create an alias. The preferred way is to give a75 name to the parameter in the <code class="command">CREATE FUNCTION</code> command,76 for example:77</p><pre class="programlisting">78CREATE FUNCTION sales_tax(subtotal real) RETURNS real AS $$79BEGIN80 RETURN subtotal * 0.06;81END;82$$ LANGUAGE plpgsql;83</pre><p>84 The other way is to explicitly declare an alias, using the85 declaration syntax86 87</p><pre class="synopsis">88<em class="replaceable"><code>name</code></em> ALIAS FOR $<em class="replaceable"><code>n</code></em>;89</pre><p>90 91 The same example in this style looks like:92</p><pre class="programlisting">93CREATE FUNCTION sales_tax(real) RETURNS real AS $$94DECLARE95 subtotal ALIAS FOR $1;96BEGIN97 RETURN subtotal * 0.06;98END;99$$ LANGUAGE plpgsql;100</pre><p>101 </p><div class="note"><h3 class="title">Note</h3><p>102 These two examples are not perfectly equivalent. In the first case,103 <code class="literal">subtotal</code> could be referenced as104 <code class="literal">sales_tax.subtotal</code>, but in the second case it could not.105 (Had we attached a label to the inner block, <code class="literal">subtotal</code> could106 be qualified with that label, instead.)107 </p></div><p>108 Some more examples:109</p><pre class="programlisting">110CREATE FUNCTION instr(varchar, integer) RETURNS integer AS $$111DECLARE112 v_string ALIAS FOR $1;113 index ALIAS FOR $2;114BEGIN115 -- some computations using v_string and index here116END;117$$ LANGUAGE plpgsql;118 119 120CREATE FUNCTION concat_selected_fields(in_t sometablename) RETURNS text AS $$121BEGIN122 RETURN in_t.f1 || in_t.f3 || in_t.f5 || in_t.f7;123END;124$$ LANGUAGE plpgsql;125</pre><p>126 </p><p>127 When a <span class="application">PL/pgSQL</span> function is declared128 with output parameters, the output parameters are given129 <code class="literal">$<em class="replaceable"><code>n</code></em></code> names and optional130 aliases in just the same way as the normal input parameters. An131 output parameter is effectively a variable that starts out NULL;132 it should be assigned to during the execution of the function.133 The final value of the parameter is what is returned. For instance,134 the sales-tax example could also be done this way:135 136</p><pre class="programlisting">137CREATE FUNCTION sales_tax(subtotal real, OUT tax real) AS $$138BEGIN139 tax := subtotal * 0.06;140END;141$$ LANGUAGE plpgsql;142</pre><p>143 144 Notice that we omitted <code class="literal">RETURNS real</code> — we could have145 included it, but it would be redundant.146 </p><p>147 To call a function with <code class="literal">OUT</code> parameters, omit the148 output parameter(s) in the function call:149</p><pre class="programlisting">150SELECT sales_tax(100.00);151</pre><p>152 </p><p>153 Output parameters are most useful when returning multiple values.154 A trivial example is:155 156</p><pre class="programlisting">157CREATE FUNCTION sum_n_product(x int, y int, OUT sum int, OUT prod int) AS $$158BEGIN159 sum := x + y;160 prod := x * y;161END;162$$ LANGUAGE plpgsql;163 164SELECT * FROM sum_n_product(2, 4);165 sum | prod166-----+------167 6 | 8168</pre><p>169 170 As discussed in <a class="xref" href="xfunc-sql.html#XFUNC-OUTPUT-PARAMETERS" title="38.5.4. SQL Functions with Output Parameters">Section 38.5.4</a>, this171 effectively creates an anonymous record type for the function's172 results. If a <code class="literal">RETURNS</code> clause is given, it must say173 <code class="literal">RETURNS record</code>.174 </p><p>175 This also works with procedures, for example:176 177</p><pre class="programlisting">178CREATE PROCEDURE sum_n_product(x int, y int, OUT sum int, OUT prod int) AS $$179BEGIN180 sum := x + y;181 prod := x * y;182END;183$$ LANGUAGE plpgsql;184</pre><p>185 186 In a call to a procedure, all the parameters must be specified. For187 output parameters, <code class="literal">NULL</code> may be specified when188 calling the procedure from plain SQL:189</p><pre class="programlisting">190CALL sum_n_product(2, 4, NULL, NULL);191 sum | prod192-----+------193 6 | 8194</pre><p>195 196 However, when calling a procedure197 from <span class="application">PL/pgSQL</span>, you should instead write a198 variable for any output parameter; the variable will receive the result199 of the call. See <a class="xref" href="plpgsql-control-structures.html#PLPGSQL-STATEMENTS-CALLING-PROCEDURE" title="43.6.3. Calling a Procedure">Section 43.6.3</a>200 for details.201 </p><p>202 Another way to declare a <span class="application">PL/pgSQL</span> function203 is with <code class="literal">RETURNS TABLE</code>, for example:204 205</p><pre class="programlisting">206CREATE FUNCTION extended_sales(p_itemno int)207RETURNS TABLE(quantity int, total numeric) AS $$208BEGIN209 RETURN QUERY SELECT s.quantity, s.quantity * s.price FROM sales AS s210 WHERE s.itemno = p_itemno;211END;212$$ LANGUAGE plpgsql;213</pre><p>214 215 This is exactly equivalent to declaring one or more <code class="literal">OUT</code>216 parameters and specifying <code class="literal">RETURNS SETOF217 <em class="replaceable"><code>sometype</code></em></code>.218 </p><p>219 When the return type of a <span class="application">PL/pgSQL</span> function220 is declared as a polymorphic type (see221 <a class="xref" href="extend-type-system.html#EXTEND-TYPES-POLYMORPHIC" title="38.2.5. Polymorphic Types">Section 38.2.5</a>), a special222 parameter <code class="literal">$0</code> is created. Its data type is the actual223 return type of the function, as deduced from the actual input types.224 This allows the function to access its actual return type225 as shown in <a class="xref" href="plpgsql-declarations.html#PLPGSQL-DECLARATION-TYPE" title="43.3.3. Copying Types">Section 43.3.3</a>.226 <code class="literal">$0</code> is initialized to null and can be modified by227 the function, so it can be used to hold the return value if desired,228 though that is not required. <code class="literal">$0</code> can also be229 given an alias. For example, this function works on any data type230 that has a <code class="literal">+</code> operator:231 232</p><pre class="programlisting">233CREATE FUNCTION add_three_values(v1 anyelement, v2 anyelement, v3 anyelement)234RETURNS anyelement AS $$235DECLARE236 result ALIAS FOR $0;237BEGIN238 result := v1 + v2 + v3;239 RETURN result;240END;241$$ LANGUAGE plpgsql;242</pre><p>243 </p><p>244 The same effect can be obtained by declaring one or more output parameters as245 polymorphic types. In this case the246 special <code class="literal">$0</code> parameter is not used; the output247 parameters themselves serve the same purpose. For example:248 249</p><pre class="programlisting">250CREATE FUNCTION add_three_values(v1 anyelement, v2 anyelement, v3 anyelement,251 OUT sum anyelement)252AS $$253BEGIN254 sum := v1 + v2 + v3;255END;256$$ LANGUAGE plpgsql;257</pre><p>258 </p><p>259 In practice it might be more useful to declare a polymorphic function260 using the <code class="type">anycompatible</code> family of types, so that automatic261 promotion of the input arguments to a common type will occur.262 For example:263 264</p><pre class="programlisting">265CREATE FUNCTION add_three_values(v1 anycompatible, v2 anycompatible, v3 anycompatible)266RETURNS anycompatible AS $$267BEGIN268 RETURN v1 + v2 + v3;269END;270$$ LANGUAGE plpgsql;271</pre><p>272 273 With this example, a call such as274 275</p><pre class="programlisting">276SELECT add_three_values(1, 2, 4.7);277</pre><p>278 279 will work, automatically promoting the integer inputs to numeric.280 The function using <code class="type">anyelement</code> would require you to281 cast the three inputs to the same type manually.282 </p></div><div class="sect2" id="PLPGSQL-DECLARATION-ALIAS"><div class="titlepage"><div><div><h3 class="title">43.3.2. <code class="literal">ALIAS</code> <a href="#PLPGSQL-DECLARATION-ALIAS" class="id_link">#</a></h3></div></div></div><pre class="synopsis">283<em class="replaceable"><code>newname</code></em> ALIAS FOR <em class="replaceable"><code>oldname</code></em>;284</pre><p>285 The <code class="literal">ALIAS</code> syntax is more general than is suggested in the286 previous section: you can declare an alias for any variable, not just287 function parameters. The main practical use for this is to assign288 a different name for variables with predetermined names, such as289 <code class="varname">NEW</code> or <code class="varname">OLD</code> within290 a trigger function.291 </p><p>292 Examples:293</p><pre class="programlisting">294DECLARE295 prior ALIAS FOR old;296 updated ALIAS FOR new;297</pre><p>298 </p><p>299 Since <code class="literal">ALIAS</code> creates two different ways to name the same300 object, unrestricted use can be confusing. It's best to use it only301 for the purpose of overriding predetermined names.302 </p></div><div class="sect2" id="PLPGSQL-DECLARATION-TYPE"><div class="titlepage"><div><div><h3 class="title">43.3.3. Copying Types <a href="#PLPGSQL-DECLARATION-TYPE" class="id_link">#</a></h3></div></div></div><pre class="synopsis">303<em class="replaceable"><code>variable</code></em>%TYPE304</pre><p>305 <code class="literal">%TYPE</code> provides the data type of a variable or306 table column. You can use this to declare variables that will hold307 database values. For example, let's say you have a column named308 <code class="literal">user_id</code> in your <code class="literal">users</code>309 table. To declare a variable with the same data type as310 <code class="literal">users.user_id</code> you write:311</p><pre class="programlisting">312user_id users.user_id%TYPE;313</pre><p>314 </p><p>315 By using <code class="literal">%TYPE</code> you don't need to know the data316 type of the structure you are referencing, and most importantly,317 if the data type of the referenced item changes in the future (for318 instance: you change the type of <code class="literal">user_id</code>319 from <code class="type">integer</code> to <code class="type">real</code>), you might not need320 to change your function definition.321 </p><p>322 <code class="literal">%TYPE</code> is particularly valuable in polymorphic323 functions, since the data types needed for internal variables can324 change from one call to the next. Appropriate variables can be325 created by applying <code class="literal">%TYPE</code> to the function's326 arguments or result placeholders.327 </p></div><div class="sect2" id="PLPGSQL-DECLARATION-ROWTYPES"><div class="titlepage"><div><div><h3 class="title">43.3.4. Row Types <a href="#PLPGSQL-DECLARATION-ROWTYPES" class="id_link">#</a></h3></div></div></div><pre class="synopsis">328<em class="replaceable"><code>name</code></em> <em class="replaceable"><code>table_name</code></em><code class="literal">%ROWTYPE</code>;329<em class="replaceable"><code>name</code></em> <em class="replaceable"><code>composite_type_name</code></em>;330</pre><p>331 A variable of a composite type is called a <em class="firstterm">row</em>332 variable (or <em class="firstterm">row-type</em> variable). Such a variable333 can hold a whole row of a <code class="command">SELECT</code> or <code class="command">FOR</code>334 query result, so long as that query's column set matches the335 declared type of the variable.336 The individual fields of the row value337 are accessed using the usual dot notation, for example338 <code class="literal">rowvar.field</code>.339 </p><p>340 A row variable can be declared to have the same type as the rows of341 an existing table or view, by using the342 <em class="replaceable"><code>table_name</code></em><code class="literal">%ROWTYPE</code>343 notation; or it can be declared by giving a composite type's name.344 (Since every table has an associated composite type of the same name,345 it actually does not matter in <span class="productname">PostgreSQL</span> whether you346 write <code class="literal">%ROWTYPE</code> or not. But the form with347 <code class="literal">%ROWTYPE</code> is more portable.)348 </p><p>349 Parameters to a function can be350 composite types (complete table rows). In that case, the351 corresponding identifier <code class="literal">$<em class="replaceable"><code>n</code></em></code> will be a row variable, and fields can352 be selected from it, for example <code class="literal">$1.user_id</code>.353 </p><p>354 Here is an example of using composite types. <code class="structname">table1</code>355 and <code class="structname">table2</code> are existing tables having at least the356 mentioned fields:357 358</p><pre class="programlisting">359CREATE FUNCTION merge_fields(t_row table1) RETURNS text AS $$360DECLARE361 t2_row table2%ROWTYPE;362BEGIN363 SELECT * INTO t2_row FROM table2 WHERE ... ;364 RETURN t_row.f1 || t2_row.f3 || t_row.f5 || t2_row.f7;365END;366$$ LANGUAGE plpgsql;367 368SELECT merge_fields(t.*) FROM table1 t WHERE ... ;369</pre><p>370 </p></div><div class="sect2" id="PLPGSQL-DECLARATION-RECORDS"><div class="titlepage"><div><div><h3 class="title">43.3.5. Record Types <a href="#PLPGSQL-DECLARATION-RECORDS" class="id_link">#</a></h3></div></div></div><pre class="synopsis">371<em class="replaceable"><code>name</code></em> RECORD;372</pre><p>373 Record variables are similar to row-type variables, but they have no374 predefined structure. They take on the actual row structure of the375 row they are assigned during a <code class="command">SELECT</code> or <code class="command">FOR</code> command. The substructure376 of a record variable can change each time it is assigned to.377 A consequence of this is that until a record variable is first assigned378 to, it has no substructure, and any attempt to access a379 field in it will draw a run-time error.380 </p><p>381 Note that <code class="literal">RECORD</code> is not a true data type, only a placeholder.382 One should also realize that when a <span class="application">PL/pgSQL</span>383 function is declared to return type <code class="type">record</code>, this is not quite the384 same concept as a record variable, even though such a function might385 use a record variable to hold its result. In both cases the actual row386 structure is unknown when the function is written, but for a function387 returning <code class="type">record</code> the actual structure is determined when the388 calling query is parsed, whereas a record variable can change its row389 structure on-the-fly.390 </p></div><div class="sect2" id="PLPGSQL-DECLARATION-COLLATION"><div class="titlepage"><div><div><h3 class="title">43.3.6. Collation of <span class="application">PL/pgSQL</span> Variables <a href="#PLPGSQL-DECLARATION-COLLATION" class="id_link">#</a></h3></div></div></div><a id="id-1.8.8.5.14.2" class="indexterm"></a><p>391 When a <span class="application">PL/pgSQL</span> function has one or more392 parameters of collatable data types, a collation is identified for each393 function call depending on the collations assigned to the actual394 arguments, as described in <a class="xref" href="collation.html" title="24.2. Collation Support">Section 24.2</a>. If a collation is395 successfully identified (i.e., there are no conflicts of implicit396 collations among the arguments) then all the collatable parameters are397 treated as having that collation implicitly. This will affect the398 behavior of collation-sensitive operations within the function.399 For example, consider400 401</p><pre class="programlisting">402CREATE FUNCTION less_than(a text, b text) RETURNS boolean AS $$403BEGIN404 RETURN a < b;405END;406$$ LANGUAGE plpgsql;407 408SELECT less_than(text_field_1, text_field_2) FROM table1;409SELECT less_than(text_field_1, text_field_2 COLLATE "C") FROM table1;410</pre><p>411 412 The first use of <code class="function">less_than</code> will use the common collation413 of <code class="structfield">text_field_1</code> and <code class="structfield">text_field_2</code> for414 the comparison, while the second use will use <code class="literal">C</code> collation.415 </p><p>416 Furthermore, the identified collation is also assumed as the collation of417 any local variables that are of collatable types. Thus this function418 would not work any differently if it were written as419 420</p><pre class="programlisting">421CREATE FUNCTION less_than(a text, b text) RETURNS boolean AS $$422DECLARE423 local_a text := a;424 local_b text := b;425BEGIN426 RETURN local_a < local_b;427END;428$$ LANGUAGE plpgsql;429</pre><p>430 </p><p>431 If there are no parameters of collatable data types, or no common432 collation can be identified for them, then parameters and local variables433 use the default collation of their data type (which is usually the434 database's default collation, but could be different for variables of435 domain types).436 </p><p>437 A local variable of a collatable data type can have a different collation438 associated with it by including the <code class="literal">COLLATE</code> option in its439 declaration, for example440 441</p><pre class="programlisting">442DECLARE443 local_a text COLLATE "en_US";444</pre><p>445 446 This option overrides the collation that would otherwise be447 given to the variable according to the rules above.448 </p><p>449 Also, of course explicit <code class="literal">COLLATE</code> clauses can be written inside450 a function if it is desired to force a particular collation to be used in451 a particular operation. For example,452 453</p><pre class="programlisting">454CREATE FUNCTION less_than_c(a text, b text) RETURNS boolean AS $$455BEGIN456 RETURN a < b COLLATE "C";457END;458$$ LANGUAGE plpgsql;459</pre><p>460 461 This overrides the collations associated with the table columns,462 parameters, or local variables used in the expression, just as would463 happen in a plain SQL command.464 </p></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="plpgsql-structure.html" title="43.2. Structure of PL/pgSQL">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-expressions.html" title="43.4. Expressions">Next</a></td></tr><tr><td width="40%" align="left" valign="top">43.2. Structure of <span class="application">PL/pgSQL</span> </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.4. Expressions</td></tr></table></div></body></html>