| PostgreSQL 8.1.23 Documentation | ||||
|---|---|---|---|---|
| Prev | Fast Backward | Chapter 36. PL/pgSQL - SQL Procedural Language | Fast Forward | Next |
All variables used in a block must be declared in the declarations section of the block. (The only exception is that the loop variable of a FOR loop iterating over a range of integer values is automatically declared as an integer 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 [ 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, so that its value remains constant for the duration of the block. 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.
The default value is evaluated every time the block is entered. 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'; user_id CONSTANT integer := 10;
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, which was the only way available before PostgreSQL 8.0, 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;
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.
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;
As discussed in Section 32.4.3, this effectively creates an anonymous record type for the function's results. If a RETURNS clause is given, it must say RETURNS record.
When the return type of a PL/pgSQL function is declared as a polymorphic type (anyelement or anyarray), a special parameter $0 is created. Its data type is the actual return type of the function, as deduced from the actual input types (see