PL/pgSQL SELECT INTO

Summary: In this tutorial, you’ll learn how to PL/pgSQL SELECT INTO statement to select data from a table and assign it to a variable.

Overview of the PL/pgSQL SELECT INTO Statement #

The SELECT INTO statement retrieves data from a table and assigns it to a variable:

SELECT column1, column2, ...
INTO variable1, variable2, ...
FROM table_name;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this syntax:

• First, provide one or more columns of the table_name to retrieve data in the SELECT clause.
• Second, specify a variable list in the INTO clause.
• Third, specify the table name from which you retrieve data in the FROM clause.

The SELECT statement assigns the values retrieved from column1, column2, … to the corresponding variable1, variable2, … If the SELECT statement returns no rows, the variables will retain their initial values.

Besides table columns, you can use expressions in the SELECT clause. The SELECT statement may include clauses such as JOIN, GROUP BY, and HAVING.

PL/pgSQL SELECT INTO Statement Examples #

Let’s explore some examples of using the SELECT INTO statement. We’ll use the products and inventories tables:

Assigning a Value to a Variable #

The following statement uses the SELECT INTO statement to retrieve a price from the products table and assign it to a variable:

DO
$$
DECLARE
    v_price DECIMAL;
BEGIN
    SELECT price INTO v_price
    FROM products
   WHERE product_id = 1;

   RAISE NOTICE 'The price is %', v_price;
END;
$$;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output:

NOTICE:  The price is 999.99Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Assigning Values to Multiple Variables #

The following statement uses the SELECT INTO statement to retrieve a price and safety stock from the products table and assign them to multiple variables:

DO
$$
DECLARE
    v_price DECIMAL;
    v_safety_stock DECIMAL;
BEGIN
    SELECT price, safety_stock INTO v_price, v_safety_stock
    FROM products
   WHERE product_id = 1;

   RAISE NOTICE 'Price: %, Safety Stock: %', v_price, v_safety_stock;
END;
$$;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output:

NOTICE:  Price: 999.99, Safety Stock: 20Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Using the PL/pgSQL SELECT INTO Statement with JOIN Clause #

The following example shows how to use the SELECT INTO statement with JOIN to retrieve the inventory amount of the Apple iPhone 15 Pro Max product and assign it to a variable:

DO
$$
DECLARE
    v_inventory_amount DECIMAL;
BEGIN
   SELECT quantity * price 
    INTO  v_inventory_amount 
   FROM inventories
   JOIN products USING (product_id)
   WHERE product_name = 'Apple iPhone 15 Pro Max';

   RAISE NOTICE 'iPhone 15 Pro Max Inventory Amount: $%', v_inventory_amount;
END;
$$;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Type Copying #

PL/pgSQL allows declaring a variable with the type copying from a table column using the following syntax:

variable_name table_name.column_name%TYPE;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

For example, you can declare the v_price variable with the type copying from the price column of the products table:

v_price products.price%TYPE;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Type copying ensures variable types align with the values retrieved from the columns that they reference.

When the column’s data types change, the variables will automatically inherit the new data types without needing manual code changes.

Additionally, you can declare a variable with the type copying from another variable with the fllowing syntax

variable1 variable2%TYPE;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The following example shows how to declare variables by copying types from table columns:

DO
$$
DECLARE
    v_price products.price%TYPE;
    v_safety_stock products.safety_stock%TYPE;
BEGIN
    SELECT price, safety_stock INTO v_price, v_safety_stock
    FROM products
   WHERE product_id = 1;

   RAISE NOTICE 'Price: %, Safety Stock: %', v_price, v_safety_stock;
END;
$$;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output:

NOTICE:  Price: 999.99, Safety Stock: 20Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Summary #

• Use the SELECT INTO statement to retrieve data from a table and assign it to variables.
• Leverage type copying to write more robust and maintainable code.

Quiz #

Exercises #

Exercise 1: Basic SELECT INTO statement #

Write a PL/pgSQL block to retrieve the price from the products table with id 100 and display it using the RAISE NOTICE statement.

DO
$$
DECLARE
    v_price DECIMAL;
BEGIN
    SELECT price INTO v_price
    FROM products
   WHERE product_id = 100;

   RAISE NOTICE 'The price is %', v_price;
END;
$$;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Exercise 2: Multiple value retrieval and Type Copying #

Write a PL/pgSQL block to retrieve the min, max, and average product prices from the products table and display them using the RAISE NOTICE statement. Use type copying for the min and max variables.

DO
$$
DECLARE
    v_min products.price%TYPE;
    v_max products.price%TYPE;
    v_avg DEC;
BEGIN
    SELECT MIN(price), MAX(price), ROUND(AVG(price),2)
    INTO v_min, v_max, v_avg
    FROM products;

   RAISE NOTICE 'Min: $%, Max: $%, Average: $%', v_min, v_max, v_avg;
END;
$$;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)