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
– wildplasser, Commented Apr 27, 2020 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
– Master, Commented Apr 27, 2020 at 12:33

Vivek Kumar Singh · Accepted Answer · 2023-09-21 10:22:04Z

1

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

edited Sep 21, 2023 at 10:22

Vivek Kumar Singh

3,3601 gold badge19 silver badges30 bronze badges

answered Apr 27, 2020 at 12:42

Vibhor Kumar

3141 silver badge2 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Master Over a year ago

just please fix, instead FOR r in SELECT there should be FOR rec in SELECT

bobmarksie Over a year ago

please update to use rec

Collectives™ on Stack Overflow

postgres execute sql in resulting rows

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related