Dynamic SQL with EXECUTE in PostgreSQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is Dynamic SQL in PostgreSQL?

Dynamic SQL is a way to build and run SQL commands as text strings at runtime, allowing flexible queries that can change based on input or conditions.

beginner

How do you execute a dynamic SQL command inside a PL/pgSQL function?

You use the EXECUTE statement followed by a string containing the SQL command you want to run dynamically.

intermediate

Why should you use quote_ident() or quote_literal() when building dynamic SQL?

These functions safely add identifiers or values to the SQL string, preventing errors and SQL injection by properly quoting them.

intermediate

What happens if you try to use EXECUTE with a variable that is not a string?

EXECUTE requires a string argument. If you pass a non-string variable, you must convert it to text first, or it will cause an error.

intermediate

Can you return results directly from EXECUTE in PL/pgSQL?

Yes, you can use EXECUTE with INTO to store the result of a dynamic query into a variable.

Which PostgreSQL statement runs a dynamic SQL command stored in a string variable?

A EXECUTE

B RUN

C CALL

D PERFORM

EXECUTE runs a SQL command given as a string in PL/pgSQL.

What function should you use to safely add a table name to a dynamic SQL string?

A quote_literal()

B quote_ident()

C to_char()

D cast()

quote_ident() safely quotes identifiers like table or column names.

How do you capture the result of a dynamic SELECT query in PL/pgSQL?

A EXECUTE ... INTO variable

B PERFORM ... INTO variable

C CALL ... INTO variable

D SELECT ... INTO variable

EXECUTE ... INTO stores the result of a dynamic query into a variable.

What is a risk if you build dynamic SQL without quoting identifiers or literals?

A No risk at all

B Faster query execution

C Automatic optimization

D Syntax errors and SQL injection

Not quoting can cause syntax errors and security risks like SQL injection.

Which data type must EXECUTE receive as input?

A Integer

B Boolean

C Text or string

D Date

EXECUTE requires a string containing the SQL command.

Explain how to safely build and run a dynamic SQL query in PostgreSQL using EXECUTE.

Think about how to avoid errors and injection risks.

Describe a scenario where dynamic SQL with EXECUTE is useful and how you would implement it.

Imagine you want to query different tables based on user input.