Database Reference
In-Depth Information
Declaring the %TYPE attribute
The
%TYPE
attribute is helpful to store values of a database object, usually a table
column. This means declaring a variable with this attribute will store the value of
the same data type it referenced. This is even more helpful if in future your column's
data type gets changed. It's declared in the following manner:
variable_name table_name.column_name%TYPE
Using the
warehouse_db
database, we have a column name
amount
in the
history
table in the
record
schema. So, we can declare a variable referencing this column.
This will look like the following:
amount history.history%TYPE
Declaring the row-type and record type
variables
A row-type variable declares a row with the structure of an existing user-deined
table or view using the
table_name%ROWTYPE
notation; otherwise, it can be declared
by giving a composite type's name. The ields of the row can be accessed with the dot
notation, for example,
rowvariable.field
.
The variable declared with
ROWTYPE
can store a row of a
SELECT
or
FOR
query result,
as long as that query's column type matches with that of the variable declared.
Record types are similar to row-types with the exception that they do not have any
predeined structure and can accept a row from any table. This means that they can
change their structure each time they are assigned to a row. They can be used in the
SELECT
and
FOR
commands to store database rows. Accessing it before it's assigned
or if it contains any results will lead to a runtime error.
Statements and expressions
Statements and expressions are important constructs for any programming language.
PL/pgSQL code is composed of a variety of statements and expressions. Remember
that statements are commands that do not return a result and expressions evaluate to
return a result. An expression can also be distinguished as an internal component of
a statement and a very useful one for data manipulation.
