How do I evaluate a cursor loop variable as a table name?

Question

FOR _r IN curs1 LOOP
    ALTER TABLE QUOTE_IDENT(_r.table_name) ALTER COLUMN company_uuid SET NOT NULL; 
END LOOP;

I am trying to convert the table name to an identifier so I can use it dynamically.

The error is: ERROR: syntax error at or near "("

Answer 1

Values can be parameterized for the core DML statements SELECT, INSERT, UPDATE, and DELETE.

But identifiers (or syntax elements) cannot be parameterized anywhere in SQL. In PL/pgSQL you can use dynamic SQL with EXECUTE like Anton suggested. I.e., concatenate the whole command as string and then execute. See:

• Error when setting n_distinct using a plpgsql variable

Optionally use format() for convenience:

EXECUTE format('ALTER TABLE %I ALTER COLUMN company_uuid SET NOT NULL', _r.table_name);

With the %I specifier for identifiers, unless _r.table_name is already quoted properly. See:

• Format specifier for integer variables in format() for EXECUTE?

Answer 2

Seemingly it is invalid to use functions in the command. You may use dynamic sql instead.

EXECUTE 'ALTER TABLE ' || QUOTE_IDENT(_r.table_name) || ' ALTER COLUMN company_uuid SET NOT NULL;';