PostgreSQL – Copying Data Types

When working with PostgreSQL, you can define a variable that directly references the data type of a column in a table or the data type of another variable. This feature is useful when you want to maintain consistency and avoid repetitive changes to your code whenever the data type of a column is altered.

Let us better understand this concept in PostgreSQL along with syntax and examples.

Syntax to Reference Column Data Types

The below syntax refers to the data type of a column:

variable_name table_name.column_name%TYPE;

This syntax allows you to automatically copy the data type of the specified column into the variable.

Syntax to Reference Variable Data Types

The below syntax refers to the data type of another variable:

variable_name variable%TYPE;

This is useful when you need multiple variables of the same type, ensuring that any future changes in data type only need to be updated in one place.

Example

In this example, we will build a table (say, ‘City’) with a column (say, ‘name’) that has a CHAR data type and create a variable (say, ‘city_name’) and refer to the data type of the column as the data type of the variable.

city_name city.name%TYPE := 'Delhi';

Now let’s check the data type of the variable

SELECT pg_typeof(city_name);

This will establish that the variable is of type CHAR.

By using copying type feature, you receive the following advantages:

• First, you don’t need to care about the data type of the column. You declare a variable to just hold the values of that column in a query.
• Second, when the data type of the column changes, you don’t need to change the variable declaration in the function to adapt to the new changes.
• Third, you can refer to the type of variables to the data type of function arguments to create polymorphic functions since the type of internal variables can change from one call to the next.

Assigning aliases to variables

PostgreSQL also allows you to assign aliases to variables, which can be particularly helpful in trigger procedures where variables have predetermined names such as NEW or OLD. You can create aliases to improve code readability:

new_name ALIAS FOR old_name;

This feature is mainly used in triggers to give more meaningful names to variables that represent data before or after an update.

Example: Using %TYPE and Aliases in a Trigger

CREATE OR REPLACE FUNCTION log_city_changes()
RETURNS TRIGGER AS $$
DECLARE
  old_name ALIAS FOR OLD.name;
  new_name ALIAS FOR NEW.name;
  city_name City.name%TYPE;
BEGIN
  city_name := new_name;
  IF city_name IS DISTINCT FROM old_name THEN
    INSERT INTO City_Logs (old_name, new_name, change_time)
    VALUES (old_name, city_name, NOW());
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER city_update_trigger
AFTER UPDATE ON City
FOR EACH ROW EXECUTE FUNCTION log_city_changes();

In this example:

• The ‘log_city_changes’ function uses aliases to rename the ‘OLD.name’ and ‘NEW.name’ variables, making the code more readable.
• We also use the ‘%TYPE’ feature to ensure that ‘city_name’ always matches the data type of the ‘name’ column in the ‘City’ table.