How to Use Dynamic Values as Table Names in PostgreSQL?

Using Dynamic Values as Table Names in PostgreSQL

When dealing with database operations, it's often necessary to use dynamic values as table names. This can be achieved through dynamic SQL, where the table name is determined during query execution.

In PostgreSQL, dynamic SQL can be implemented using the PL/PgSQL EXECUTE statement within a DO block or PL/PgSQL function. Ordinary SQL does not support dynamic SQL.

For example, consider the following goal: using the result of a query to dynamically determine the table name for a subsequent query.

Query to Obtain the Table Name:

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

Desired Subsequent Query:

CREATE TABLE (SELECT 'backup_' || TO_CHAR(CURRENT_DATE,'yyyy-mm-dd')) AS * SELECT FROM backup

Solution using PL/PgSQL EXECUTE:

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

Explanation:

The format(...) function is used to construct the dynamic SQL statement. The %I format specifier ensures proper quoting of the table/column name.

Note:

For literal values, it's recommended to use EXECUTE ... USING instead of format(...) with %L. However, for identifier like table names, the format %I pattern is a convenient alternative to using quote_ident.

The above is the detailed content of How to Use Dynamic Values as Table Names in PostgreSQL?. For more information, please follow other related articles on the PHP Chinese website!