DO blocks for anonymous code in PostgreSQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to start a DO block in PostgreSQL.

PostgreSQL

DO [1] $$ BEGIN RAISE NOTICE 'Hello, world!'; END; $$;

Drag options to blanks, or click blank then click option'

ALANGUAGE javascript

BLANGUAGE sql

CLANGUAGE python

DLANGUAGE plpgsql

Attempts:

3 left

💡 Hint

Common Mistakes

Using a language not supported by PostgreSQL DO blocks.

Omitting the LANGUAGE keyword.

The DO block in PostgreSQL requires specifying the language, usually plpgsql for procedural code.

2fill in blank

medium

Complete the code to declare a variable inside a DO block.

PostgreSQL

DO LANGUAGE plpgsql $$ DECLARE my_var [1]; BEGIN my_var := 10; RAISE NOTICE 'Value: %', my_var; END; $$;

Drag options to blanks, or click blank then click option'

Ainteger

Bdate

Cboolean

Dtext

Attempts:

3 left

💡 Hint

Common Mistakes

Using a text type for a numeric value.

Not declaring the variable type.

Variables in PL/pgSQL must have a type. Here, my_var is an integer.

3fill in blank

hard

Fix the error in the DO block by completing the missing keyword.

PostgreSQL

DO LANGUAGE plpgsql $$ [1] my_var integer := 5; BEGIN RAISE NOTICE 'Value: %', my_var; END; $$;

Drag options to blanks, or click blank then click option'

ADECLARE

BSET

CDEFINE

DCREATE

Attempts:

3 left

💡 Hint

Common Mistakes

Using SET or CREATE instead of DECLARE.

Placing variable declarations inside BEGIN without DECLARE.

In PL/pgSQL DO blocks, variables must be declared in a DECLARE section before the BEGIN block.

4fill in blank

hard

Fill the blank to complete the DO block that loops from 1 to 3 and raises a notice.

PostgreSQL

DO LANGUAGE plpgsql $$ DECLARE i [1]; BEGIN FOR i IN 1..3 LOOP RAISE NOTICE 'Number: %', i; END LOOP; END; $$;

Drag options to blanks, or click blank then click option'

Atext

Bboolean

Cinteger

Ddate

Attempts:

3 left

💡 Hint

Common Mistakes

Using text or boolean types for loop counters.

Not declaring the variable before BEGIN.

The variable i is declared as an integer and used in the FOR loop.

5fill in blank

hard

Fill all three blanks to complete a DO block that declares a variable, assigns a value, and raises a notice.

PostgreSQL

DO LANGUAGE plpgsql $$ DECLARE my_var [1]; BEGIN my_var := [2]; RAISE NOTICE 'Value is %', [3]; END; $$;

Drag options to blanks, or click blank then click option'

Ainteger

B20

Cmy_var

Dtext

Attempts:

3 left

💡 Hint

Common Mistakes

Assigning a string to an integer variable.

Printing a value instead of the variable.

The variable my_var is declared as integer, assigned 20, and printed.

Practice

1. What is the main purpose of a DO block in PostgreSQL?

easy

A. To define a new permanent function

B. To create a new table in the database

C. To execute a SELECT query and return results

D. To run anonymous procedural code immediately without creating a permanent function

Solution

1. Step 1: Understand the role of DO blocks

   DO blocks allow running procedural code immediately without saving it as a function.

2. Step 2: Compare with other options

   Creating tables or functions is done with other commands, and DO blocks do not return query results.

3. Final Answer:

   To run anonymous procedural code immediately without creating a permanent function -> Option D

4. Quick Check:

   DO blocks = anonymous immediate code execution [OK]

Hint: DO blocks run code immediately without saving functions [OK]

Common Mistakes:

• Thinking DO blocks create permanent functions
• Confusing DO blocks with SELECT queries
• Assuming DO blocks create tables

2. Which of the following is the correct syntax to start a DO block in PostgreSQL?

easy

A. DO $$ BEGIN END $$ LANGUAGE plpgsql;

B. DO LANGUAGE plpgsql BEGIN END;

C. DO BEGIN $$ END LANGUAGE plpgsql;

D. DO $$ LANGUAGE plpgsql BEGIN END $$;

Solution

1. Step 1: Recall the correct DO block syntax

   The DO block uses dollar quoting $$ to enclose the code, with LANGUAGE plpgsql specified after the block.

2. Step 2: Check each option

   DO $$ BEGIN END $$ LANGUAGE plpgsql; correctly places $$ around BEGIN...END and specifies LANGUAGE plpgsql after the block.

3. Final Answer:

   DO $$ BEGIN END $$ LANGUAGE plpgsql; -> Option A

4. Quick Check:

   DO block syntax = DO $$ code $$ LANGUAGE plpgsql; [OK]

Hint: Use DO $$ ... $$ LANGUAGE plpgsql; to start DO blocks [OK]

Common Mistakes:

• Placing LANGUAGE plpgsql before BEGIN
• Not using dollar quoting $$
• Misordering keywords in the DO block

3. What will be the output of this DO block?

DO $$
BEGIN
  RAISE NOTICE 'Hello, PostgreSQL!';
END
$$ LANGUAGE plpgsql;

medium

A. It prints 'Hello, PostgreSQL!' as a notice message

B. It returns a result set with 'Hello, PostgreSQL!'

C. It causes a syntax error

D. It creates a permanent function named 'Hello, PostgreSQL!'

Solution

1. Step 1: Understand RAISE NOTICE in DO blocks

   RAISE NOTICE outputs a message to the client as an informational notice, not a query result.

2. Step 2: Analyze the DO block behavior

   The block runs immediately and prints the notice message but does not return rows or create functions.

3. Final Answer:

   It prints 'Hello, PostgreSQL!' as a notice message -> Option A

4. Quick Check:

   RAISE NOTICE outputs messages, not query results [OK]

Hint: RAISE NOTICE shows messages, no query output [OK]

Common Mistakes:

• Expecting query result rows
• Thinking it creates a function
• Confusing notice with error

4. Identify the error in this DO block:

DO $$
BEGIN
  PERFORM 1/0;
END
$$ LANGUAGE plpgsql;

medium

A. Syntax error due to missing semicolon

B. Division by zero runtime error

C. Missing LANGUAGE specification

D. Invalid use of PERFORM keyword

Solution

1. Step 1: Analyze the code inside DO block

   The statement PERFORM 1/0 attempts to divide 1 by zero, which is not allowed.

2. Step 2: Identify the error type

   This causes a runtime error (division by zero), not a syntax error or missing keyword.

3. Final Answer:

   Division by zero runtime error -> Option B

4. Quick Check:

   1/0 causes runtime error, not syntax [OK]

Hint: Check for runtime errors like division by zero [OK]

Common Mistakes:

• Confusing runtime error with syntax error
• Ignoring division by zero possibility
• Assuming PERFORM is invalid here

5. You want to update a table users to set active = false for all users who haven't logged in for over a year. Which DO block correctly performs this task?

hard

A. DO $$ BEGIN UPDATE users SET active = false WHERE last_login > NOW() - INTERVAL '1 year'; END $$ LANGUAGE plpgsql;

B. DO $$ BEGIN SELECT * FROM users WHERE last_login < NOW() - INTERVAL '1 year'; END $$ LANGUAGE plpgsql;

C. DO $$ BEGIN UPDATE users SET active = false WHERE last_login < NOW() - INTERVAL '1 year'; END $$ LANGUAGE plpgsql;

D. DO $$ UPDATE users SET active = false WHERE last_login < NOW() - INTERVAL '1 year'; END $$ LANGUAGE plpgsql;

Solution

1. Step 1: Check the DO block structure and logic

   DO $$ BEGIN UPDATE users SET active = false WHERE last_login < NOW() - INTERVAL '1 year'; END $$ LANGUAGE plpgsql; correctly uses BEGIN...END with an UPDATE statement and the right condition for last_login older than 1 year.

2. Step 2: Verify other options

   DO $$ BEGIN SELECT * FROM users WHERE last_login < NOW() - INTERVAL '1 year'; END $$ LANGUAGE plpgsql; only selects rows, no update. DO $$ UPDATE users SET active = false WHERE last_login < NOW() - INTERVAL '1 year'; END $$ LANGUAGE plpgsql; misses BEGIN...END block. DO $$ BEGIN UPDATE users SET active = false WHERE last_login > NOW() - INTERVAL '1 year'; END $$ LANGUAGE plpgsql; uses wrong condition (greater than instead of less than).

3. Final Answer:

   DO $$ BEGIN UPDATE users SET active = false WHERE last_login < NOW() - INTERVAL '1 year'; END $$ LANGUAGE plpgsql; -> Option C

4. Quick Check:

   Correct DO block with UPDATE and condition [OK]

Hint: Use BEGIN...END with UPDATE and correct WHERE condition [OK]

Common Mistakes:

• Omitting BEGIN...END block
• Using wrong comparison operator in WHERE
• Using SELECT instead of UPDATE