How Can I Dynamically Generate Table Names in PostgreSQL?

Dynamically Generating Table Names in PostgreSQL

In SQL, dynamic SQL is not supported except in the PL/PgSQL variant, which can create dynamic queries. To use the result of a query as a table name in another query, you can utilize the PL/PgSQL EXECUTE statement.

Here's an example:

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

The %I format specifier in the format function properly quotes identifiers, including table names, eliminating the need for explicit quoting with quote_ident.

To prevent SQL injection vulnerabilities, consider using the EXECUTE ... USING statement for literals instead of format(...) with %L. For identifiers like table/column names, the conciseness of the %I pattern remains a useful alternative to quote_ident.

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