Postgresql Declare Variable

• We use a constant name and with a particular data type for a memory location in the PostgreSQL database.

Syntax:

variable_name data_type [:= expression]; 

• Now when you run this, you can declare and assign the variable with a value.

For example:

first_name VARCHAR(50) := 'Richard';

last_name  VARCHAR(50) := 'Rambo';

payment    NUMERIC(11,2) := 150.5;

• There are several data types which can be stored in this postgres as:

1. Boolean, char, varchar, and text.
2. Integer and floating-point number.
3. Date, time, Timestamp, interval and serial or big serial.
4. Particular types such as network address and geometric data.
5. UUID for storing Universally Unique Identifiers
6. Array for storing array strings, numbers, etc.
7. JSON stores JSON data
8. hstore stores key-value pair

• Aliasing to variables:

Alias is used mostly for allotting a new name to a predetermined name.

This method changes the predetermined name to a new name for user convenience. 

Syntax:

newname ALIAS FOR oldname;

To change the old name to the new user-desired name, we use this method.

For example:

User_number ALIAS FOR UserID;

Hence this will update the user number to userid.

• Copying data types:

This function mainly used for declaring variables that hold similar data type values.

Without knowing the data type also, we can declare the variable in the column as it stores the value in the form of that column data type.

%TYPE will declare the data type of the column to the new variable assigned.

%TYPE used for polymorphic functions which change their data type from one form to another every time you run a program. 

Syntax:

variable_name table_name.column_name%TYPE;

For example:

New_state_name states.name%TYPE := 'Hyderabad';