PostgreSQL – How to Run ALTER TABLE on Multiple Tables Sequentially

alter-tabledynamic-sqlfunctionsplpgsqlpostgresql

I have a schema (called import) where data are imported as tables, and a function that processes them all. After that I want to move these tables away in another schema (called data_archive). The existing function works great but I can't find a way to change the schema; here's a function I wrote to do this:

My idea was to create a list of tables according to criteria provided by a select on a table made for this management, then loop over this list and do a Alter table xxxx SET schema data_archive:

CREATE OR REPLACE FUNCTION archive_datasets()
  RETURNS VOID AS
$BODY$
DECLARE
   table_rec  record;

BEGIN

   FOR table_rec IN
      SELECT t.table_name
      FROM information_schema.tables AS t
      WHERE table_schema = 'import'
      AND quote_ident(t.table_name) IN (
              SELECT "table_name"
              FROM data_sets 
              WHERE status IN ('Processed', 'Archived', 'Deleted')
              ) 

   LOOP
     ALTER TABLE quote_ident(table_rec.table_name) SET schema data_archived;
   END LOOP;

END;
$BODY$
  LANGUAGE plpgsql;

But I obtain this error message: 

ERROR:  syntax error at or near "("
LINE 28:  ALTER TABLE quote_ident(table_rec.table_name) SET schema ...

So my question is how to pass the name of the table to this ALTER TABLE query? Or what am I doing wrong?

I use PostgreSQL 9.3.

Best Answer

The problem is that you cannot use parameters as object names (tables, columns, etc.) in straight SQL statements, like your ALTER TABLE above.

I use 'straight' here as an opposite of 'dynamic' - the thing you need:

...
loop
    EXECUTE format($$ALTER TABLE %I SET SCHEMA TO data_archived$$, 
                   table_rec.table_name);
END loop;
...

Notes:

  • here I am using dollar quoting when building the dynamic query. This allows me to use 'normal' syntax, as opposed to multiplicating single quotes, for example (not present in this example). This way most editors will highlight the statements nicely.
  • I also use format() with the %I format specifier to have the object name properly quoted if necessary. This approach is far more readable than building the query with concatenation of string constants and some quote_ident() calls. It is present in versions 9.1 and newer.
  • I have a feeling that you compare stuff like quote_ident('This_table') IN ('This_table'), which won't be true. Either use quote_ident() on both sides - or it is easier to just omit quoting and compare the string values.
Related Question