[Solved] What is wrong with this dynamic SQL usage?DECLARE col text := 'price'; total numeric; BEGIN EXECUTE format('SELECT sum(%L) FROM sales', col) INTO total; RAISE NOTICE 'Total: %', total; END; | PostgreSQL

PostgreSQL - Advanced PL/pgSQL

What is wrong with this dynamic SQL usage?

DECLARE
  col text := 'price';
  total numeric;
BEGIN
  EXECUTE format('SELECT sum(%L) FROM sales', col) INTO total;
  RAISE NOTICE 'Total: %', total;
END;

AINTO cannot be used with EXECUTE

BUsing %L quotes the column name as a literal, causing an error

CRAISE NOTICE syntax is incorrect

DVariable col should be integer, not text

Step-by-Step Solution

Solution:

Step 1: Understand format specifiers
%L quotes the argument as a literal string, which is wrong for column names.
Step 2: Correct usage
%I should be used to safely insert identifiers like column names.
Final Answer:
Using %L quotes the column name as a literal, causing an error -> Option B
Quick Check:
Use %I for identifiers, not %L [OK]

Quick Trick: Use %I for identifiers in format(), not %L [OK]

Common Mistakes:

Using %L for column names
Thinking INTO is invalid with EXECUTE
Misunderstanding RAISE NOTICE syntax

Master "Advanced PL/pgSQL" in PostgreSQL

9 interactive learning modes - each teaches the same concept differently

Learn Why Deep Visual Try Challenge Project Recall Time

What is wrong with this dynamic SQL usage?

Step 1: Understand format specifiers

Step 2: Correct usage

Final Answer:

Quick Check:

Want More Practice?