How to Dynamically Create PostgreSQL Tables Using a String as the Table Name?

Dynamically Using a Returned String as a Table Name in PostgreSQL

In PostgreSQL, you may encounter a scenario where you need to dynamically generate a table name from a query result. This can be achieved by leveraging the EXECUTE statement in combination with PL/PgSQL.

To accomplish this, you can employ the following steps:

1. Enclose the dynamic SQL statement in a DO block or PL/PgSQL function:

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

2. Utilize format specifiers:
   The format() function provides %I (identifier) and %L (literal) format specifiers for proper quoting of identifiers and literals, respectively.

   In this example, %I surrounds the table name 'backup_' || to_char(CURRENT_DATE, 'yyyy-mm-dd') with double quotes, ensuring it's correctly recognized as an identifier.

3. Execute the statement:
   The EXECUTE statement dynamically executes the generated SQL statement.

By following these steps, you can dynamically create a table using a string returned from a query in PostgreSQL.

The above is the detailed content of How to Dynamically Create PostgreSQL Tables Using a String as the Table Name?. For more information, please follow other related articles on the PHP Chinese website!