[Solved] You want to write a PL/pgSQL function that takes a table name and a column name as inputs and returns the maximum... Which approach correctly uses dynamic SQL with EXECUTE to achieve this safely? | PostgreSQL

PostgreSQL - Advanced PL/pgSQL

You want to write a PL/pgSQL function that takes a table name and a column name as inputs and returns the maximum value in that column. Which approach correctly uses dynamic SQL with EXECUTE to achieve this safely?

AEXECUTE 'SELECT max(' || col_name || ') FROM ' || table_name INTO result;

BEXECUTE format('SELECT max(%I) FROM %I', col_name, table_name) INTO result;

CEXECUTE 'SELECT max($1) FROM $2' USING col_name, table_name INTO result;

DEXECUTE 'SELECT max(:col_name) FROM :table_name' INTO result;

Step-by-Step Solution

Solution:

Step 1: Understand safe dynamic SQL construction
Using format() with %I safely quotes identifiers like column and table names to avoid SQL injection.
Step 2: Check other options for safety and correctness
EXECUTE 'SELECT max(' || col_name || ') FROM ' || table_name INTO result; concatenates strings directly (unsafe). EXECUTE 'SELECT max($1) FROM $2' USING col_name, table_name INTO result; uses placeholders incorrectly for identifiers. EXECUTE 'SELECT max(:col_name) FROM :table_name' INTO result; uses invalid syntax.
Final Answer:
EXECUTE format('SELECT max(%I) FROM %I', col_name, table_name) INTO result; -> Option B
Quick Check:
Use format() with %I for identifiers in EXECUTE [OK]

Quick Trick: Use format() with %I to safely insert identifiers in EXECUTE [OK]

Common Mistakes:

Concatenating identifiers without quoting
Using placeholders for table/column names
Incorrect EXECUTE syntax with named parameters

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

Which approach correctly uses dynamic SQL with EXECUTE to achieve this safely?

Step 1: Understand safe dynamic SQL construction

Step 2: Check other options for safety and correctness

Final Answer:

Quick Check:

Want More Practice?