1
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
8
CONTENT="Modular DocBook HTML Stylesheet Version 1.79"><LINK
10
HREF="mailto:pgsql-docs@postgresql.org"><LINK
12
TITLE="PostgreSQL 9.1beta1 Documentation"
13
HREF="index.html"><LINK
15
TITLE="PL/pgSQL - SQL Procedural Language"
16
HREF="plpgsql.html"><LINK
18
TITLE="Structure of PL/pgSQL"
19
HREF="plpgsql-structure.html"><LINK
22
HREF="plpgsql-expressions.html"><LINK
25
HREF="stylesheet.css"><META
26
HTTP-EQUIV="Content-Type"
27
CONTENT="text/html; charset=ISO-8859-1"><META
29
CONTENT="2011-04-27T21:20:33"></HEAD
35
SUMMARY="Header navigation table"
47
>PostgreSQL 9.1beta1 Documentation</A
56
TITLE="Structure of PL/pgSQL"
57
HREF="plpgsql-structure.html"
66
TITLE="PL/pgSQL - SQL Procedural Language"
80
> Procedural Language</TD
86
TITLE="PL/pgSQL - SQL Procedural Language"
96
HREF="plpgsql-expressions.html"
110
NAME="PLPGSQL-DECLARATIONS"
111
>39.3. Declarations</A
114
> All variables used in a block must be declared in the
115
declarations section of the block.
116
(The only exceptions are that the loop variable of a <TT
120
iterating over a range of integer values is automatically declared as an
121
integer variable, and likewise the loop variable of a <TT
125
iterating over a cursor's result is automatically declared as a
132
> variables can have any SQL data type, such as
146
> Here are some examples of variable declarations:
148
CLASS="PROGRAMLISTING"
152
myrow tablename%ROWTYPE;
153
myfield tablename.columnname%TYPE;
158
> The general syntax of a variable declaration is:
187
> { DEFAULT | := } <TT
198
> clause, if given, specifies the initial value assigned
199
to the variable when the block is entered. If the <TT
203
is not given then the variable is initialized to the
211
> option prevents the variable from being
212
assigned to after initialization, so that its value will remain constant
213
for the duration of the block.
217
> option specifies a collation to use for the
219
HREF="plpgsql-declarations.html#PLPGSQL-DECLARATION-COLLATION"
226
is specified, an assignment of a null value results in a run-time
227
error. All variables declared as <TT
231
must have a nonnull default value specified.
234
> A variable's default value is evaluated and assigned to the variable
235
each time the block is entered (not just once per function call).
236
So, for example, assigning <TT
239
> to a variable of type
243
> causes the variable to have the
244
time of the current function call, not the time when the function was
250
CLASS="PROGRAMLISTING"
251
>quantity integer DEFAULT 32;
252
url varchar := 'http://mysite.com';
253
user_id CONSTANT integer := 10;</PRE
261
NAME="PLPGSQL-DECLARATION-PARAMETERS"
262
>39.3.1. Declaring Function Parameters</A
265
> Parameters passed to functions are named with the identifiers
273
etc. Optionally, aliases can be declared for
283
parameter names for increased readability. Either the alias or the
284
numeric identifier can then be used to refer to the parameter value.
287
> There are two ways to create an alias. The preferred way is to give a
288
name to the parameter in the <TT
294
CLASS="PROGRAMLISTING"
295
>CREATE FUNCTION sales_tax(subtotal real) RETURNS real AS $$
297
RETURN subtotal * 0.06;
299
$$ LANGUAGE plpgsql;</PRE
301
The other way, which was the only way available before
305
> 8.0, is to explicitly
306
declare an alias, using the declaration syntax
323
The same example in this style looks like:
325
CLASS="PROGRAMLISTING"
326
>CREATE FUNCTION sales_tax(real) RETURNS real AS $$
328
subtotal ALIAS FOR $1;
330
RETURN subtotal * 0.06;
332
$$ LANGUAGE plpgsql;</PRE
342
> These two examples are not perfectly equivalent. In the first case,
346
> could be referenced as
349
>sales_tax.subtotal</TT
350
>, but in the second case it could not.
351
(Had we attached a label to the inner block, <TT
355
be qualified with that label, instead.)
360
> Some more examples:
362
CLASS="PROGRAMLISTING"
363
>CREATE FUNCTION instr(varchar, integer) RETURNS integer AS $$
365
v_string ALIAS FOR $1;
368
-- some computations using v_string and index here
373
CREATE FUNCTION concat_selected_fields(in_t sometablename) RETURNS text AS $$
375
RETURN in_t.f1 || in_t.f3 || in_t.f5 || in_t.f7;
377
$$ LANGUAGE plpgsql;</PRE
384
> function is declared
385
with output parameters, the output parameters are given
395
aliases in just the same way as the normal input parameters. An
396
output parameter is effectively a variable that starts out NULL;
397
it should be assigned to during the execution of the function.
398
The final value of the parameter is what is returned. For instance,
399
the sales-tax example could also be done this way:
402
CLASS="PROGRAMLISTING"
403
>CREATE FUNCTION sales_tax(subtotal real, OUT tax real) AS $$
405
tax := subtotal * 0.06;
407
$$ LANGUAGE plpgsql;</PRE
410
Notice that we omitted <TT
413
> — we could have
414
included it, but it would be redundant.
417
> Output parameters are most useful when returning multiple values.
418
A trivial example is:
421
CLASS="PROGRAMLISTING"
422
>CREATE FUNCTION sum_n_product(x int, y int, OUT sum int, OUT prod int) AS $$
427
$$ LANGUAGE plpgsql;</PRE
431
HREF="xfunc-sql.html#XFUNC-OUTPUT-PARAMETERS"
434
effectively creates an anonymous record type for the function's
438
> clause is given, it must say
445
> Another way to declare a <SPAN
455
CLASS="PROGRAMLISTING"
456
>CREATE FUNCTION extended_sales(p_itemno int)
457
RETURNS TABLE(quantity int, total numeric) AS $$
459
RETURN QUERY SELECT quantity, quantity * price FROM sales
460
WHERE itemno = p_itemno;
462
$$ LANGUAGE plpgsql;</PRE
465
This is exactly equivalent to declaring one or more <TT
469
parameters and specifying <TT
481
> When the return type of a <SPAN
485
function is declared as a polymorphic type (<TT
499
a special parameter <TT
503
is created. Its data type is the actual return type of the function,
504
as deduced from the actual input types (see <A
505
HREF="extend-type-system.html#EXTEND-TYPES-POLYMORPHIC"
508
This allows the function to access its actual return type
510
HREF="plpgsql-declarations.html#PLPGSQL-DECLARATION-TYPE"
516
> is initialized to null and can be modified by
517
the function, so it can be used to hold the return value if desired,
518
though that is not required. <TT
522
given an alias. For example, this function works on any data type
529
CLASS="PROGRAMLISTING"
530
>CREATE FUNCTION add_three_values(v1 anyelement, v2 anyelement, v3 anyelement)
531
RETURNS anyelement AS $$
535
result := v1 + v2 + v3;
538
$$ LANGUAGE plpgsql;</PRE
542
> The same effect can be had by declaring one or more output parameters as
543
polymorphic types. In this case the
547
> parameter is not used; the output
548
parameters themselves serve the same purpose. For example:
551
CLASS="PROGRAMLISTING"
552
>CREATE FUNCTION add_three_values(v1 anyelement, v2 anyelement, v3 anyelement,
558
$$ LANGUAGE plpgsql;</PRE
567
NAME="PLPGSQL-DECLARATION-ALIAS"
590
> syntax is more general than is suggested in the
591
previous section: you can declare an alias for any variable, not just
592
function parameters. The main practical use for this is to assign
593
a different name for variables with predetermined names, such as
606
CLASS="PROGRAMLISTING"
609
updated ALIAS FOR new;</PRE
616
> creates two different ways to name the same
617
object, unrestricted use can be confusing. It's best to use it only
618
for the purpose of overriding predetermined names.
626
NAME="PLPGSQL-DECLARATION-TYPE"
627
>39.3.3. Copying Types</A
641
> provides the data type of a variable or
642
table column. You can use this to declare variables that will hold
643
database values. For example, let's say you have a column named
651
table. To declare a variable with the same data type as
657
CLASS="PROGRAMLISTING"
658
>user_id users.user_id%TYPE;</PRE
665
> you don't need to know the data
666
type of the structure you are referencing, and most importantly,
667
if the data type of the referenced item changes in the future (for
668
instance: you change the type of <TT
678
>), you might not need
679
to change your function definition.
685
> is particularly valuable in polymorphic
686
functions, since the data types needed for internal variables can
687
change from one call to the next. Appropriate variables can be
688
created by applying <TT
692
arguments or result placeholders.
700
NAME="PLPGSQL-DECLARATION-ROWTYPES"
701
>39.3.4. Row Types</A
727
>composite_type_name</I
731
> A variable of a composite type is called a <I
738
> variable). Such a variable
739
can hold a whole row of a <TT
746
query result, so long as that query's column set matches the
747
declared type of the variable.
748
The individual fields of the row value
749
are accessed using the usual dot notation, for example
756
> A row variable can be declared to have the same type as the rows of
757
an existing table or view, by using the
767
notation; or it can be declared by giving a composite type's name.
768
(Since every table has an associated composite type of the same name,
769
it actually does not matter in <SPAN
776
> or not. But the form with
783
> Parameters to a function can be
784
composite types (complete table rows). In that case, the
785
corresponding identifier <TT
793
> will be a row variable, and fields can
794
be selected from it, for example <TT
800
> Only the user-defined columns of a table row are accessible in a
801
row-type variable, not the OID or other system columns (because the
802
row could be from a view). The fields of the row type inherit the
803
table's field size or precision for data types such as
815
> Here is an example of using composite types. <TT
822
> are existing tables having at least the
826
CLASS="PROGRAMLISTING"
827
>CREATE FUNCTION merge_fields(t_row table1) RETURNS text AS $$
829
t2_row table2%ROWTYPE;
831
SELECT * INTO t2_row FROM table2 WHERE ... ;
832
RETURN t_row.f1 || t2_row.f3 || t_row.f5 || t2_row.f7;
836
SELECT merge_fields(t.*) FROM table1 t WHERE ... ;</PRE
845
NAME="PLPGSQL-DECLARATION-RECORDS"
846
>39.3.5. Record Types</A
857
> Record variables are similar to row-type variables, but they have no
858
predefined structure. They take on the actual row structure of the
859
row they are assigned during a <TT
865
> command. The substructure
866
of a record variable can change each time it is assigned to.
867
A consequence of this is that until a record variable is first assigned
868
to, it has no substructure, and any attempt to access a
869
field in it will draw a run-time error.
875
> is not a true data type, only a placeholder.
876
One should also realize that when a <SPAN
880
function is declared to return type <TT
883
>, this is not quite the
884
same concept as a record variable, even though such a function might
885
use a record variable to hold its result. In both cases the actual row
886
structure is unknown when the function is written, but for a function
890
> the actual structure is determined when the
891
calling query is parsed, whereas a record variable can change its row
892
structure on-the-fly.
900
NAME="PLPGSQL-DECLARATION-COLLATION"
901
>39.3.6. Collation of <SPAN
910
> function has one or more
911
parameters of collatable data types, a collation is identified for each
912
function call depending on the collations assigned to the actual
913
arguments, as described in <A
914
HREF="collation.html"
917
successfully identified (i.e., there are no conflicts of implicit
918
collations among the arguments) then all the collatable parameters are
919
treated as having that collation implicitly. This will affect the
920
behavior of collation-sensitive operations within the function.
921
For example, consider
924
CLASS="PROGRAMLISTING"
925
>CREATE FUNCTION less_than(a text, b text) RETURNS boolean AS $$
931
SELECT less_than(text_field_1, text_field_2) FROM table1;
932
SELECT less_than(text_field_1, text_field_2 COLLATE "C") FROM table1;</PRE
935
The first use of <CODE
938
> will use the common collation
946
the comparison, while the second use will use <TT
952
> Furthermore, the identified collation is also assumed as the collation of
953
any local variables that are of collatable types. Thus this function
954
would not work any differently if it were written as
957
CLASS="PROGRAMLISTING"
958
>CREATE FUNCTION less_than(a text, b text) RETURNS boolean AS $$
963
RETURN local_a < local_b;
965
$$ LANGUAGE plpgsql;</PRE
969
> If there are no parameters of collatable data types, or no common
970
collation can be identified for them, then parameters and local variables
971
use the default collation of their data type (which is usually the
972
database's default collation, but could be different for variables of
976
> A local variable of a collatable data type can have a different collation
977
associated with it by including the <TT
981
declaration, for example
984
CLASS="PROGRAMLISTING"
986
local_a text COLLATE "en_US";</PRE
989
This option overrides the collation that would otherwise be
990
given to the variable according to the rules above.
993
> Also, of course explicit <TT
996
> clauses can be written inside
997
a function if it is desired to force a particular collation to be used in
998
a particular operation. For example,
1001
CLASS="PROGRAMLISTING"
1002
>CREATE FUNCTION less_than_c(a text, b text) RETURNS boolean AS $$
1004
RETURN a < b COLLATE "C";
1006
$$ LANGUAGE plpgsql;</PRE
1009
This overrides the collations associated with the table columns,
1010
parameters, or local variables used in the expression, just as would
1011
happen in a plain SQL command.
1020
SUMMARY="Footer navigation table"
1031
HREF="plpgsql-structure.html"
1049
HREF="plpgsql-expressions.html"
b'\\ No newline at end of file'