Скачать или смотреть How to Dynamically Add Values to a PostgreSQL Enum in PL/pgSQL

Learn how to use dynamic SQL in PL/pgSQL to add values to an existing enum type in PostgreSQL without encountering syntax errors.
---
This video is based on the question https://stackoverflow.com/q/74583895/ asked by the user 'Dorialean' ( https://stackoverflow.com/u/16632154/ ) and on the answer https://stackoverflow.com/a/74584019/ provided by the user 'Pavel Stehule' ( https://stackoverflow.com/u/406691/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: How to add value in enum in plpgsql function

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
How to Dynamically Add Values to a PostgreSQL Enum in PL/pgSQL

Enums (enumerated types) in PostgreSQL are a great way to hold a fixed set of values, like a list of professions. Sometimes, though, you might want to expand that list by adding new values dynamically. This can be a bit tricky to implement using PL/pgSQL functions, particularly because of the restrictions on how Data Definition Language (DDL) commands, such as ALTER TYPE, can be executed. In this guide, we'll explore how to effectively add values to an enum type in a PostgreSQL function without running into syntax errors.

The Problem

You might want to write a simple function to add new values to an existing enum type called “profession.” Here's the code you might start with:

[[See Video to Reveal this Text or Code Snippet]]

However, this code will throw an error:

[[See Video to Reveal this Text or Code Snippet]]

The underlying issue is that the ALTER command is considered a Data Definition Language (DDL) command. DDL commands don’t permit parameterization in the way you’d like, which prevents them from running with a variable like p. Simply replacing p with a string would work, but that defeats the purpose. So, how do you make this work?

The Solution: Dynamic SQL

To work around the limitations of DDL commands in PL/pgSQL, you can use dynamic SQL. This method allows you to build your command as a string and then execute it. Here’s how you can implement it:

Step-by-Step Implementation

Define the Function: Create a function that takes a text parameter, which represents the value you want to add to the enum.

Use the EXECUTE command: This will execute a string that you format to include your parameter safely.

Format the String: Use the format function to construct your command while ensuring that it is properly escaped.

Updated Function Code

Here’s the working code for your function with dynamic SQL:

[[See Video to Reveal this Text or Code Snippet]]

Explanation of the Code

EXECUTE statement: This allows you to run a raw command in PL/pgSQL.

format function: This method prepares the command string. The %L in the string will correctly escape your input variable p, preventing SQL injection attacks and syntax issues.

RETURN statement: Although it is not strictly necessary in this context, including it can improve readability and allows you to clearly conclude the function's operation.

Conclusion

Adding values to an existing enum type in PostgreSQL through PL/pgSQL doesn’t have to be complicated. By using dynamic SQL, you can create robust and flexible functions that handle parameterization for DDL commands. The example provided will help you manage enum types effectively, allowing for dynamic updates to your database's schema.

If you're working with PostgreSQL functions, remember that handling DDL commands correctly is essential for maintaining your database's structure without running into syntax issues. Now you can confidently add new professions to your enum!