postgres execute sql in resulting rows

Question

postgesql 9.6.17

There are result rows from Renaming multiple columns in PostgreSQL

they contain some commands like

alter table .....
alter table .....

how to immediately exec them in sql like

SELECT
    EXEC SQL 'ALTER TABLE ' || tab_name || ' RENAME COLUMN '
    || quote_ident(column_name) || ' TO '
    || lower(quote_ident( column_name)) || ';' commit
FROM (
    SELECT
        quote_ident(table_schema) || '.' || quote_ident(table_name) as tab_name,
        column_name
    FROM information_schema.columns  
    WHERE 
            table_schema = 'public'
) sub;

but example ↑ fails

I think you mean `EXECUTE` Also: your code is incomplete and cannot be tested. — wildplasser, Apr 27 '20 at 12:07
example is fully complete. i want to do lower() for all column names of all my tables, so i got an alter table - query in rows, but it is huge, and i ask, how to execute them in sql — Master, Apr 27 '20 at 12:33

score 1 · Accepted Answer · answered Apr 27 '20 at 12:42

You can use the DO statement for executing this. Something like given below:

DO $$
DECLARE rec TEXT;
BEGIN
  FOR r in SELECT
            'ALTER TABLE ' || tab_name || ' RENAME COLUMN '
            || quote_ident(column_name) || ' TO '
            || lower(quote_ident( column_name))
          FROM (
              SELECT
                  quote_ident(table_schema) || '.' || 
                  quote_ident(table_name) as tab_name,
                  column_name
                  FROM information_schema.columns  
              WHERE 
                   table_schema = 'public'
            ) sub
  LOOP
    EXECUTE rec;
  END LOOP;
END $$;

For more detail refer the following link: https://www.postgresql.org/docs/9.6/sql-do.html

just please fix, instead FOR r in SELECT there should be FOR rec in SELECT — Master, Apr 27 '20 at 12:53

postgres execute sql in resulting rows

1 Answers1