Convert HTML docs to more streamlined TXT

[ai-pg] / full-docs / txt / plpgsql-declarations.txt

41.3. Declarations #

   41.3.1. Declaring Function Parameters
   41.3.2. ALIAS
   41.3.3. Copying Types
   41.3.4. Row Types
   41.3.5. Record Types
   41.3.6. Collation of PL/pgSQL Variables

   All variables used in a block must be declared in the declarations
   section of the block. (The only exceptions are that the loop variable
   of a FOR loop iterating over a range of integer values is automatically
   declared as an integer variable, and likewise the loop variable of a
   FOR loop iterating over a cursor's result is automatically declared as
   a record variable.)

   PL/pgSQL variables can have any SQL data type, such as integer,
   varchar, and char.

   Here are some examples of variable declarations:
user_id integer;
quantity numeric(5);
url varchar;
myrow tablename%ROWTYPE;
myfield tablename.columnname%TYPE;
arow RECORD;

   The general syntax of a variable declaration is:
name [ CONSTANT ] type [ COLLATE collation_name ] [ NOT NULL ] [ { DEFAULT | :=
| = } expression ];

   The DEFAULT clause, if given, specifies the initial value assigned to
   the variable when the block is entered. If the DEFAULT clause is not
   given then the variable is initialized to the SQL null value. The
   CONSTANT option prevents the variable from being assigned to after
   initialization, so that its value will remain constant for the duration
   of the block. The COLLATE option specifies a collation to use for the
   variable (see Section 41.3.6). If NOT NULL is specified, an assignment
   of a null value results in a run-time error. All variables declared as
   NOT NULL must have a nonnull default value specified. Equal (=) can be
   used instead of PL/SQL-compliant :=.

   A variable's default value is evaluated and assigned to the variable
   each time the block is entered (not just once per function call). So,
   for example, assigning now() to a variable of type timestamp causes the
   variable to have the time of the current function call, not the time
   when the function was precompiled.

   Examples:
quantity integer DEFAULT 32;
url varchar := 'http://mysite.com';
transaction_time CONSTANT timestamp with time zone := now();

   Once declared, a variable's value can be used in later initialization
   expressions in the same block, for example:
DECLARE
  x integer := 1;
  y integer := x + 1;

41.3.1. Declaring Function Parameters #

   Parameters passed to functions are named with the identifiers $1, $2,
   etc. Optionally, aliases can be declared for $n parameter names for
   increased readability. Either the alias or the numeric identifier can
   then be used to refer to the parameter value.

   There are two ways to create an alias. The preferred way is to give a
   name to the parameter in the CREATE FUNCTION command, for example:
CREATE FUNCTION sales_tax(subtotal real) RETURNS real AS $$
BEGIN
    RETURN subtotal * 0.06;
END;
$$ LANGUAGE plpgsql;

   The other way is to explicitly declare an alias, using the declaration
   syntax
name ALIAS FOR $n;

   The same example in this style looks like:
CREATE FUNCTION sales_tax(real) RETURNS real AS $$
DECLARE
    subtotal ALIAS FOR $1;
BEGIN
    RETURN subtotal * 0.06;
END;
$$ LANGUAGE plpgsql;

Note

   These two examples are not perfectly equivalent. In the first case,
   subtotal could be referenced as sales_tax.subtotal, but in the second
   case it could not. (Had we attached a label to the inner block,
   subtotal could be qualified with that label, instead.)

   Some more examples:
CREATE FUNCTION instr(varchar, integer) RETURNS integer AS $$
DECLARE
    v_string ALIAS FOR $1;
    index ALIAS FOR $2;
BEGIN
    -- some computations using v_string and index here
END;
$$ LANGUAGE plpgsql;


CREATE FUNCTION concat_selected_fields(in_t sometablename) RETURNS text AS $$
BEGIN
    RETURN in_t.f1 || in_t.f3 || in_t.f5 || in_t.f7;
END;
$$ LANGUAGE plpgsql;

   When a PL/pgSQL function is declared with output parameters, the output
   parameters are given $n names and optional aliases in just the same way
   as the normal input parameters. An output parameter is effectively a
   variable that starts out NULL; it should be assigned to during the
   execution of the function. The final value of the parameter is what is
   returned. For instance, the sales-tax example could also be done this
   way:
CREATE FUNCTION sales_tax(subtotal real, OUT tax real) AS $$
BEGIN
    tax := subtotal * 0.06;
END;
$$ LANGUAGE plpgsql;

   Notice that we omitted RETURNS real — we could have included it, but it
   would be redundant.

   To call a function with OUT parameters, omit the output parameter(s) in
   the function call:
SELECT sales_tax(100.00);

   Output parameters are most useful when returning multiple values. A
   trivial example is:
CREATE FUNCTION sum_n_product(x int, y int, OUT sum int, OUT prod int) AS $$
BEGIN
    sum := x + y;
    prod := x * y;
END;
$$ LANGUAGE plpgsql;

SELECT * FROM sum_n_product(2, 4);
 sum | prod
-----+------
   6 |    8

   As discussed in Section 36.5.4, this effectively creates an anonymous
   record type for the function's results. If a RETURNS clause is given,
   it must say RETURNS record.

   This also works with procedures, for example:
CREATE PROCEDURE sum_n_product(x int, y int, OUT sum int, OUT prod int) AS $$
BEGIN
    sum := x + y;
    prod := x * y;
END;
$$ LANGUAGE plpgsql;

   In a call to a procedure, all the parameters must be specified. For
   output parameters, NULL may be specified when calling the procedure
   from plain SQL:
CALL sum_n_product(2, 4, NULL, NULL);
 sum | prod
-----+------
   6 |    8

   However, when calling a procedure from PL/pgSQL, you should instead
   write a variable for any output parameter; the variable will receive
   the result of the call. See Section 41.6.3 for details.

   Another way to declare a PL/pgSQL function is with RETURNS TABLE, for
   example:
CREATE FUNCTION extended_sales(p_itemno int)
RETURNS TABLE(quantity int, total numeric) AS $$
BEGIN
    RETURN QUERY SELECT s.quantity, s.quantity * s.price FROM sales AS s
                 WHERE s.itemno = p_itemno;
END;
$$ LANGUAGE plpgsql;

   This is exactly equivalent to declaring one or more OUT parameters and
   specifying RETURNS SETOF sometype.

   When the return type of a PL/pgSQL function is declared as a
   polymorphic type (see Section 36.2.5), a special parameter $0 is
   created. Its data type is the actual return type of the function, as
   deduced from the actual input types. This allows the function to access
   its actual return type as shown in Section 41.3.3. $0 is initialized to
   null and can be modified by the function, so it can be used to hold the
   return value if desired, though that is not required. $0 can also be
   given an alias. For example, this function works on any data type that
   has a + operator:
CREATE FUNCTION add_three_values(v1 anyelement, v2 anyelement, v3 anyelement)
RETURNS anyelement AS $$
DECLARE
    result ALIAS FOR $0;
BEGIN
    result := v1 + v2 + v3;
    RETURN result;
END;
$$ LANGUAGE plpgsql;

   The same effect can be obtained by declaring one or more output
   parameters as polymorphic types. In this case the special $0 parameter
   is not used; the output parameters themselves serve the same purpose.
   For example:
CREATE FUNCTION add_three_values(v1 anyelement, v2 anyelement, v3 anyelement,
                                 OUT sum anyelement)
AS $$
BEGIN
    sum := v1 + v2 + v3;
END;
$$ LANGUAGE plpgsql;

   In practice it might be more useful to declare a polymorphic function
   using the anycompatible family of types, so that automatic promotion of
   the input arguments to a common type will occur. For example:
CREATE FUNCTION add_three_values(v1 anycompatible, v2 anycompatible, v3 anycompa
tible)
RETURNS anycompatible AS $$
BEGIN
    RETURN v1 + v2 + v3;
END;
$$ LANGUAGE plpgsql;

   With this example, a call such as
SELECT add_three_values(1, 2, 4.7);

   will work, automatically promoting the integer inputs to numeric. The
   function using anyelement would require you to cast the three inputs to
   the same type manually.

41.3.2. ALIAS #

newname ALIAS FOR oldname;

   The ALIAS syntax is more general than is suggested in the previous
   section: you can declare an alias for any variable, not just function
   parameters. The main practical use for this is to assign a different
   name for variables with predetermined names, such as NEW or OLD within
   a trigger function.

   Examples:
DECLARE
  prior ALIAS FOR old;
  updated ALIAS FOR new;

   Since ALIAS creates two different ways to name the same object,
   unrestricted use can be confusing. It's best to use it only for the
   purpose of overriding predetermined names.

41.3.3. Copying Types #

name table.column%TYPE
name variable%TYPE

   %TYPE provides the data type of a table column or a previously-declared
   PL/pgSQL variable. You can use this to declare variables that will hold
   database values. For example, let's say you have a column named user_id
   in your users table. To declare a variable with the same data type as
   users.user_id you write:
user_id users.user_id%TYPE;

   It is also possible to write array decoration after %TYPE, thereby
   creating a variable that holds an array of the referenced type:
user_ids users.user_id%TYPE[];
user_ids users.user_id%TYPE ARRAY[4];  -- equivalent to the above

   Just as when declaring table columns that are arrays, it doesn't matter
   whether you write multiple bracket pairs or specific array dimensions:
   PostgreSQL treats all arrays of a given element type as the same type,
   regardless of dimensionality. (See Section 8.15.1.)

   By using %TYPE you don't need to know the data type of the structure
   you are referencing, and most importantly, if the data type of the
   referenced item changes in the future (for instance: you change the
   type of user_id from integer to real), you might not need to change
   your function definition.

   %TYPE is particularly valuable in polymorphic functions, since the data
   types needed for internal variables can change from one call to the
   next. Appropriate variables can be created by applying %TYPE to the
   function's arguments or result placeholders.

41.3.4. Row Types #

name table_name%ROWTYPE;
name composite_type_name;

   A variable of a composite type is called a row variable (or row-type
   variable). Such a variable can hold a whole row of a SELECT or FOR
   query result, so long as that query's column set matches the declared
   type of the variable. The individual fields of the row value are
   accessed using the usual dot notation, for example rowvar.field.

   A row variable can be declared to have the same type as the rows of an
   existing table or view, by using the table_name%ROWTYPE notation; or it
   can be declared by giving a composite type's name. (Since every table
   has an associated composite type of the same name, it actually does not
   matter in PostgreSQL whether you write %ROWTYPE or not. But the form
   with %ROWTYPE is more portable.)

   As with %TYPE, %ROWTYPE can be followed by array decoration to declare
   a variable that holds an array of the referenced composite type.

   Parameters to a function can be composite types (complete table rows).
   In that case, the corresponding identifier $n will be a row variable,
   and fields can be selected from it, for example $1.user_id.

   Here is an example of using composite types. table1 and table2 are
   existing tables having at least the mentioned fields:
CREATE FUNCTION merge_fields(t_row table1) RETURNS text AS $$
DECLARE
    t2_row table2%ROWTYPE;
BEGIN
    SELECT * INTO t2_row FROM table2 WHERE ... ;
    RETURN t_row.f1 || t2_row.f3 || t_row.f5 || t2_row.f7;
END;
$$ LANGUAGE plpgsql;

SELECT merge_fields(t.*) FROM table1 t WHERE ... ;

41.3.5. Record Types #

name RECORD;

   Record variables are similar to row-type variables, but they have no
   predefined structure. They take on the actual row structure of the row
   they are assigned during a SELECT or FOR command. The substructure of a
   record variable can change each time it is assigned to. A consequence
   of this is that until a record variable is first assigned to, it has no
   substructure, and any attempt to access a field in it will draw a
   run-time error.

   Note that RECORD is not a true data type, only a placeholder. One
   should also realize that when a PL/pgSQL function is declared to return
   type record, this is not quite the same concept as a record variable,
   even though such a function might use a record variable to hold its
   result. In both cases the actual row structure is unknown when the
   function is written, but for a function returning record the actual
   structure is determined when the calling query is parsed, whereas a
   record variable can change its row structure on-the-fly.

41.3.6. Collation of PL/pgSQL Variables #

   When a PL/pgSQL function has one or more parameters of collatable data
   types, a collation is identified for each function call depending on
   the collations assigned to the actual arguments, as described in
   Section 23.2. If a collation is successfully identified (i.e., there
   are no conflicts of implicit collations among the arguments) then all
   the collatable parameters are treated as having that collation
   implicitly. This will affect the behavior of collation-sensitive
   operations within the function. For example, consider
CREATE FUNCTION less_than(a text, b text) RETURNS boolean AS $$
BEGIN
    RETURN a < b;
END;
$$ LANGUAGE plpgsql;

SELECT less_than(text_field_1, text_field_2) FROM table1;
SELECT less_than(text_field_1, text_field_2 COLLATE "C") FROM table1;

   The first use of less_than will use the common collation of
   text_field_1 and text_field_2 for the comparison, while the second use
   will use C collation.

   Furthermore, the identified collation is also assumed as the collation
   of any local variables that are of collatable types. Thus this function
   would not work any differently if it were written as
CREATE FUNCTION less_than(a text, b text) RETURNS boolean AS $$
DECLARE
    local_a text := a;
    local_b text := b;
BEGIN
    RETURN local_a < local_b;
END;
$$ LANGUAGE plpgsql;

   If there are no parameters of collatable data types, or no common
   collation can be identified for them, then parameters and local
   variables use the default collation of their data type (which is
   usually the database's default collation, but could be different for
   variables of domain types).

   A local variable of a collatable data type can have a different
   collation associated with it by including the COLLATE option in its
   declaration, for example
DECLARE
    local_a text COLLATE "en_US";

   This option overrides the collation that would otherwise be given to
   the variable according to the rules above.

   Also, of course explicit COLLATE clauses can be written inside a
   function if it is desired to force a particular collation to be used in
   a particular operation. For example,
CREATE FUNCTION less_than_c(a text, b text) RETURNS boolean AS $$
BEGIN
    RETURN a < b COLLATE "C";
END;
$$ LANGUAGE plpgsql;

   This overrides the collations associated with the table columns,
   parameters, or local variables used in the expression, just as would
   happen in a plain SQL command.