CODEWITHSUNDEEP
sqlpostgresqlselectexecrows
Master
Master

Reputation: 53

postgres execute sql in resulting rows

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

Upvotes: 0

Views: 851

Answers (1)

Vibhor Kumar
Vibhor Kumar

Reputation: 314

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

DO $$
DECLARE rec TEXT;
BEGIN
  FOR rec 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

Upvotes: 1

Related Questions