Data Types and How to Use Them in PL/pgSQL: INTEGER, TEXT, BOOLEAN, RECORD

In this lecture, we're gonna check out which data types are supported in PL/pgSQL and learn how to work with them efficiently. We're focusing on four main data types:

• INTEGER for working with numbers.
• TEXT for handling strings.
• BOOLEAN for logical values.
• RECORD for dynamic data structures.

We'll look at each data type with examples, so you can see how they're used in real life.

Supported Data Types in PL/pgSQL

PL/pgSQL supports all the data types you know from PostgreSQL. From simple numeric types (INTEGER, NUMERIC) to more complex ones like arrays and JSONB. Let's run through the basics.

1. Primitive types:

   • INTEGER, BIGINT, FLOAT, NUMERIC — numeric types.
   • TEXT, CHAR, VARCHAR — text types.
   • BOOLEAN — logical data type.

2. Complex types:

   • RECORD — for working with dynamic data sets.
   • ROWTYPE — for working with table row types.
   • Arrays and JSON — we'll cover those later in the course.

Working with INTEGER

INTEGER is one of the most common data types. It's used to store whole numbers. In PL/pgSQL, you can use this type for calculations, working with record IDs, and checking conditions.

Example: counting the number of records

Let's say we have a students table, and we want to know how many students are in the database.

DO $$
DECLARE
    total_students INTEGER; -- Variable to store the number of students
BEGIN
    SELECT COUNT(*) INTO total_students FROM students; -- Save the query result into the variable
    RAISE NOTICE 'Number of students: %', total_students; -- Print the message
END;
$$;

Important things to know when working with INTEGER:

• In PL/pgSQL, you assign a value to a variable using the INTO keyword.
• If you try to store a decimal value in an INTEGER, you'll get an error. For those cases, use NUMERIC or FLOAT instead.

Working with TEXT

TEXT is used to store string data. It's handy when you need to work with names, descriptions, or any other text.

Example: printing student names

In this example, we'll print the names of all students from the students table.

DO $$
DECLARE
    student_name TEXT; -- Variable for the student's name
BEGIN
    FOR student_name IN SELECT name FROM students LOOP
        RAISE NOTICE 'Student name: %', student_name; -- Print each name
    END LOOP;
END;
$$;

Useful functions for working with TEXT:

• UPPER() and LOWER() — convert to upper/lower case.
• CONCAT() — join strings together.
• LENGTH() — get the length of a string.

For example:

DO $$
DECLARE
    full_name TEXT;
BEGIN
    full_name := CONCAT('Alex', ' Min'); -- Join strings
    RAISE NOTICE 'Full name: %', UPPER(full_name); -- Print the name in upper case
END;
$$;

Working with BOOLEAN

BOOLEAN is for storing logical values: TRUE, FALSE, and NULL. This data type is especially useful for checking conditions and filtering data.

Example: checking if a student is active

Let's say you have a students table with an is_active column that shows if a student is active.

DO $$
DECLARE
    is_active BOOLEAN; -- Variable to store the active status
BEGIN
    SELECT is_active INTO is_active FROM students WHERE id = 1; -- Get the value from the table
    IF is_active THEN
        RAISE NOTICE 'Student is active!';
    ELSE
        RAISE NOTICE 'Student is NOT active.';
    END IF;
END;
$$;

Important things to know when working with BOOLEAN:

• You can use logical values directly in IF and WHILE conditions.
• The value NULL is considered "unknown" in logic, so keep that in mind when checking conditions.

Working with RECORD

RECORD is a powerful data type used to store rows of data without a predefined structure. It's especially useful when you're working with SQL query results that return multiple columns.

Example: looping through all table records

In the next example, we'll loop through all records in the students table and print each student's name and ID.

DO $$
DECLARE
    student RECORD; -- Dynamic type for storing a row of data
BEGIN
    FOR student IN SELECT id, name FROM students LOOP
        RAISE NOTICE 'ID: %, Name: %', student.id, student.name; -- Access record columns
    END LOOP;
END;
$$;

Important things to know when working with RECORD:

• RECORD variables are only filled inside a loop or when using a SELECT INTO query.
• You access columns using record.column_name.

ROWTYPE Data Types for Working with Tables

If you want to store a whole row from a table (and want strict typing), you can use the ROWTYPE type. It automatically inherits the structure of the table row.

Example: working with ROWTYPE

DO $$
DECLARE
    student students%ROWTYPE; -- Variable with the structure of a students table row
BEGIN
    SELECT * INTO student FROM students WHERE id = 1; -- Load the row data into the variable
    RAISE NOTICE 'Student name: %, Course: %', student.name, student.course;
END;
$$;

Differences Between RECORD and ROWTYPE

Practical Example

Let's write a function that returns the number of active students and their names.

CREATE FUNCTION active_students_report() RETURNS TABLE(id INT, name TEXT) AS $$
BEGIN
    RETURN QUERY
    SELECT id, name FROM students WHERE is_active = TRUE;
END;
$$ LANGUAGE plpgsql;

Calling the function:

SELECT * FROM active_students_report();

Common Mistakes When Working with Data Types

Sometimes working with data leads to mistakes. Here are a few common ones:

• Type error: trying to assign a string to an INTEGER variable (like my_var := 'abc';).
• Using NULL where TRUE or FALSE is expected.
• Incorrect use of RECORD without initializing it.

How to avoid mistakes:

• Always explicitly set variable types.
• Check the data types of table columns before writing to them.
• Use debugging commands like RAISE NOTICE.

Now you know how to work with INTEGER, TEXT, BOOLEAN, and RECORD data types in PL/pgSQL. This knowledge will help you create more complex and powerful programs in PostgreSQL's procedural programming language.