SELECT INTO and FOUND in SQL

One row lands in a variable, and FOUND says whether anything was caught.

-- INTO lands one queried row in variables; FOUND reports the catch.
DO $$
DECLARE
    buyer TEXT;
BEGIN
    SELECT c.name
    INTO buyer
    FROM customers AS c
    JOIN orders AS o ON o.customer_id = c.id
    WHERE o.id = 101;
    IF FOUND THEN
        RAISE NOTICE 'order 101 belongs to %', buyer;
    ELSE
        RAISE NOTICE 'no such order';
    END IF;
END;
$$;

How it works

1. SELECT ... INTO buyer routes the single-row result into the variable.
2. IF FOUND THEN tests whether the query matched at all.
3. The join walks from the order to its customer before landing the name.

Keywords and builtins used here

• AS
• BEGIN
• DECLARE
• DO
• ELSE
• END
• FOUND
• FROM
• IF
• INTO
• JOIN
• ON
• SELECT
• TEXT
• THEN
• WHERE
• c

The run, in numbers

Lines

17

Characters to type

316

Tokens

60

Three-star pace

70 tpm

At the three-star pace of 70 tokens a minute, this run takes about 51 seconds.

Type this snippet

Step 1 of 4 in Queries in code, step 13 of 23 in PostgreSQL Stored Procedures.

← Previous Next →