Variables and %TYPE in SQL

Declare types by hand, or borrow a column's type so they never drift apart.

-- DECLARE types by hand, or borrow a column's type with %TYPE.
DO $$
DECLARE
    kayak_price products.price%TYPE;
    markup CONSTANT NUMERIC := 1.15;
    tag TEXT := 'with markup';
BEGIN
    SELECT price
    INTO kayak_price
    FROM products
    WHERE sku = 'KAYAK-17';
    RAISE NOTICE 'kayak %: %', tag, round(kayak_price * markup, 2);
END;
$$;

How it works

1. products.price%TYPE copies the column's type — change the table, the variable follows.
2. CONSTANT ... := 1.15 fixes the markup; reassigning it would be an error.
3. := is PL/pgSQL assignment, distinct from SQL's comparison =.

Keywords and builtins used here

• BEGIN
• DECLARE
• DO
• END
• FROM
• INTO
• NUMERIC
• SELECT
• TEXT
• TYPE
• WHERE

The run, in numbers

Lines

14

Characters to type

317

Tokens

59

Three-star pace

65 tpm

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

Type this snippet

Step 2 of 4 in PL/pgSQL blocks, step 6 of 23 in PostgreSQL Stored Procedures.

← Previous Next →