

23.3. Declarations


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);

The general syntax of a variable declaration is:



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 runtime error. All variables declared as NOT NULL must have a non-NULL 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 when the function was precompiled.


quantity INTEGER DEFAULT 32;
url varchar := '''';
user_id CONSTANT INTEGER := 10;

23.3.1. Aliases for Function Parameters

name ALIAS FOR $n;


    subtotal ALIAS FOR $1;
    return subtotal * 0.06;
' LANGUAGE 'plpgsql';

    v_string ALIAS FOR $1;
    index ALIAS FOR $2;
    -- Some computations here
' LANGUAGE 'plpgsql';

23.3.2. Rowtypes

name tablename%ROWTYPE;

A variable of a composite type is called a row variable (or rowtype 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.

Presently, a row variable can only be declared using the %ROWTYPE notation; although one might expect a bare table name to work as a type declaration, it won't be accepted within PL/pgSQL functions.


Only the user-defined attributes of a table row are accessible in a rowtype variable, not OID or other system attributes (because the row could be from a view). The fields of the rowtype inherit the table's field size or precision for data types such as char(n).

23.3.3. Records

name RECORD;


Note that RECORD is not a true datatype, only a placeholder. Thus, for example, one cannot declare a function returning RECORD.

23.3.4. Attributes

Using the %TYPE and %ROWTYPE attributes, you can declare variables with the same data type or structure as another database item (e.g: a table field).


%TYPE provides the data type of a variable or database column. 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;

By using %TYPE you don't need to know the data type of the structure you are referencing, and most important, if the data type of the referenced item changes in the future (e.g: you change your table definition of user_id from INTEGER to REAL), you may not need to change your function definition.


%ROWTYPE provides the composite data type corresponding to a whole row of the specified table. table must be an existing table or view name of the database.

    users_rec users%ROWTYPE;
    user_id users.user_id%TYPE;
    user_id := users_rec.user_id;

        key ALIAS FOR $1;
        table_data cs_materialized_views%ROWTYPE;
        SELECT INTO table_data * FROM cs_materialized_views
               WHERE sort_key=key;

           RETURN false;
        END IF;
        RETURN true;
' LANGUAGE 'plpgsql';

23.3.5. RENAME

RENAME oldname TO newname;



RENAME id TO user_id;
RENAME this_var TO that_var;

Note: RENAME appears to be broken as of PostgreSQL 7.2. Fixing this is of low priority, since ALIAS covers most of the practical uses of RENAME.


Sept. 13, 2002, 4:10 p.m.

Note that ALIAS FOR does a *textual* substitution within the function. This means that e.g. compile error messages do not refer to the aliased names, but to the original name. So also look for your aliases for function arguments on the error line before dismissing the error messages as utter nonsense (as I did before spotting this).

Privacy Policy | About PostgreSQL
Copyright © 1996-2015 The PostgreSQL Global Development Group