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 "("
Add a comment |
2 Answers
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:
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:
answered Sep 11, 2021 at 22:33
Erwin Brandstetter
668k160 gold badges1.2k silver badges1.3k bronze badges
Comments
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;'; 