How Can I Create Dynamically Named Tables in PostgreSQL?

Generating Dynamic Table Names in PostgreSQL

PostgreSQL provides advanced features for dynamically generating table names based on runtime values. To create a table with a dynamic name, you must utilize the PL/PgSQL EXECUTE statement, either within a DO block or a PL/PgSQL function.

The EXECUTE statement allows you to execute dynamic SQL commands, enabling you to construct table names from returned strings. Consider the following example:

SELECT 'backup_' || TO_CHAR(CURRENT_DATE, 'yyyy-mm-dd')

This query returns a string that represents the desired table name. To use this string to create a table, you can use the following syntax:

DO
$$
BEGIN
EXECUTE format('CREATE TABLE %I AS SELECT * FROM backup', 'backup_' || to_char(CURRENT_DATE, 'yyyy-mm-dd'));
END;
$$ LANGUAGE plpgsql;

In this code, the format(...) function ensures proper quoting of the table name. The %I format specifier automatically quotes identifiers, while format(...) syntax handles literals.

For literals, it is recommended to use EXECUTE ... USING rather than format(...) with %L. However, for identifiers like table/column names, the %I pattern provides a concise and elegant alternative to manual quoting.

By using the EXECUTE statement and PL/PgSQL, you can dynamically generate table names based on returned values, allowing for greater flexibility and automation in your PostgreSQL applications.

The above is the detailed content of How Can I Create Dynamically Named Tables in PostgreSQL?. For more information, please follow other related articles on the PHP Chinese website!