Variable declaration and assignment in PostgreSQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

How do you declare a variable in a PostgreSQL PL/pgSQL block?

You declare a variable in the DECLARE section of a PL/pgSQL block using the syntax:
variable_name data_type;

beginner

What is the correct way to assign a value to a variable in PL/pgSQL?

You assign a value using the := operator, for example:
variable_name := value;

beginner

Where in a PL/pgSQL function do you declare variables?

Variables are declared in the DECLARE section, which comes after the AS or IS keyword and before the BEGIN block.

intermediate

Can you declare and assign a variable in one step in PL/pgSQL? If yes, how?

Yes, you can declare and assign a variable in one step like this:
variable_name data_type := initial_value;

beginner

What happens if you try to use a variable before declaring it in PL/pgSQL?

You will get a compilation error because PL/pgSQL requires variables to be declared before use.

In PostgreSQL PL/pgSQL, where do you declare variables?

A Inside the BEGIN block

B In the DECLARE section

C After the END keyword

D At the end of the function

Variables must be declared in the DECLARE section before the BEGIN block.

Which operator is used to assign a value to a variable in PL/pgSQL?

A =

B <-

C :=

D ==

PL/pgSQL uses ':=' for variable assignment.

How do you declare and assign a variable named count of type integer with initial value 10?

A count integer = 10;

B count := integer 10;

C integer count := 10;

D count integer := 10;

The correct syntax is 'count integer := 10;'

What will happen if you use a variable without declaring it first in PL/pgSQL?

A It will cause a compilation error

B It will cause a runtime warning

C It will default to NULL

D It will automatically declare the variable

PL/pgSQL requires variables to be declared before use, otherwise it causes a compilation error.

Which section comes immediately before the BEGIN block in a PL/pgSQL function?

A DECLARE

B EXECUTE

C SELECT

D COMMIT

The DECLARE section is where variables are declared and it comes before BEGIN.

Explain how to declare and assign a variable in a PostgreSQL PL/pgSQL function.

Think about the structure of a PL/pgSQL block and how variables are set up before the main code.

What errors might occur if you forget to declare a variable before using it in PL/pgSQL?

Consider what happens when the database engine tries to run code with unknown variables.

Practice

1. What is the correct way to declare a variable named counter of type integer in a PL/pgSQL block?

easy

A. DECLARE counter integer;

B. counter integer;

C. DECLARE counter integer = 0;

D. counter := integer;

Solution

1. Step 1: Understand variable declaration syntax

   In PL/pgSQL, variables are declared inside a DECLARE section without repeating the DECLARE keyword for each variable. The correct syntax is: variable_name data_type;

2. Step 2: Identify the correct declaration

   The line counter integer; correctly declares the variable inside the DECLARE block. Including the DECLARE keyword before each variable is incorrect.

3. Final Answer:

   counter integer; -> Option B

4. Quick Check:

   Declare variables inside DECLARE block without repeating DECLARE [OK]

Hint: Declare variables inside DECLARE block with type only [OK]

Common Mistakes:

• Omitting DECLARE keyword
• Assigning value during declaration without :=
• Using := in declaration line

2. Which of the following is the correct syntax to assign the value 10 to a variable count after it has been declared in PL/pgSQL?

easy

A. count = 10;

B. count == 10;

C. SET count = 10;

D. count := 10;

Solution

1. Step 1: Recall assignment operator in PL/pgSQL

   PL/pgSQL uses := to assign values to variables, not = or ==.

2. Step 2: Identify correct assignment syntax

   count := 10; uses count := 10; which is the correct way to assign a value.

3. Final Answer:

   count := 10; -> Option D

4. Quick Check:

   Use := for assignment in PL/pgSQL [OK]

Hint: Use := to assign values to variables [OK]

Common Mistakes:

• Using = instead of :=
• Using SET keyword incorrectly
• Using == like in other languages

3. Consider the following PL/pgSQL block:

DECLARE
  total integer := 5;
BEGIN
  total := total + 3;
  RAISE NOTICE '%', total;
END;

What will be the output when this block runs?

medium

A. 8

B. 5

C. 3

D. Error: variable not initialized

Solution

1. Step 1: Analyze initial value assignment

   The variable total is declared and initialized to 5.

2. Step 2: Calculate the new value after addition

   The statement total := total + 3; adds 3 to 5, resulting in 8.

3. Final Answer:

   8 -> Option A

4. Quick Check:

   5 + 3 = 8 [OK]

Hint: Add assigned values step-by-step to find final result [OK]

Common Mistakes:

• Ignoring initial value and assuming zero
• Confusing assignment operator with equality
• Expecting error due to missing BEGIN

4. Identify the error in the following PL/pgSQL code snippet:

DECLARE
  name text;
BEGIN
  name = 'Alice';
END;

medium

A. Incorrect assignment operator used

B. Missing DECLARE keyword

C. Variable name not declared

D. Missing semicolon after BEGIN

Solution

1. Step 1: Check variable declaration

   The variable name is declared correctly with type text.

2. Step 2: Check assignment syntax

   The assignment uses = which is incorrect in PL/pgSQL; it should use :=.

3. Final Answer:

   Incorrect assignment operator used -> Option A

4. Quick Check:

   Use := for assignment, not = [OK]

Hint: Use := for assignment, not = [OK]

Common Mistakes:

• Using = instead of :=
• Forgetting semicolon after assignment
• Confusing declaration and assignment syntax

5. You want to declare two variables a and b as integers, assign a the value 4, and then assign b the value of a multiplied by 3. Which of the following PL/pgSQL code snippets correctly does this?

hard

A. DECLARE
b integer := a * 3;
a integer := 4;
BEGIN
NULL;
END;

B. DECLARE
a integer;
b integer;
BEGIN
a = 4;
b = a * 3;
END;

C. DECLARE
a integer;
b integer;
BEGIN
a := 4;
b := a * 3;
END;

D. DECLARE
a integer := 4;
b integer;
BEGIN
b = a * 3;
END;

Solution

1. Step 1: Check variable declaration and initialization

   DECLARE a integer; b integer; BEGIN a := 4; b := a * 3; END; declares both variables without initial values, then assigns values inside the BEGIN block using correct assignment operator :=.

2. Step 2: Verify assignment and calculation

   It assigns a := 4; and then b := a * 3;, which correctly sets b to 12.

3. Final Answer:

   correctly declares and assigns variables with := -> Option C

4. Quick Check:

   Declare first, assign with := inside BEGIN [OK]

Hint: Declare variables first, assign values inside BEGIN with := [OK]

Common Mistakes:

• Using = instead of :=
• Assigning values during declaration with expressions
• Missing BEGIN block for assignments