Script to change table names in the Postgresql database

Asked

Viewed 330 times

1

I have this script:

SELECT 
n.nspname AS schema_name, 
c.relname AS table_name, 
c.reltuples::int AS num_reg 
FROM pg_class c 
    LEFT JOIN pg_namespace n ON n.oid = c.relnamespace 
    LEFT JOIN pg_tablespace t ON t.oid = c.reltablespace 
WHERE c.relkind = 'r'::char 
AND nspname NOT IN('information_schema','pg_catalog','pg_toast') 

I want to add the letter’s' to the end of each table that returns the script in a Postgresql database. How to proceed? Could someone give an idea?

1 answer

2

You can create an anonymous code block through the clause DO, briefly it is a Function that cannot have return and there is no need to write an object in the database.

In Function will be held a loop in the result of the SQL query and for each tuple the command to rename the table will be executed.

do $$
declare
    tables record;
begin
    for tables in
        SELECT 
            n.nspname AS schema_name, 
            c.relname AS table_name, 
            c.reltuples::int AS num_reg 
            FROM pg_class c 
                LEFT JOIN pg_namespace n ON n.oid = c.relnamespace 
                LEFT JOIN pg_tablespace t ON t.oid = c.reltablespace 
            WHERE c.relkind = 'r'::char 
            AND nspname NOT IN('information_schema','pg_catalog','pg_toast')
    loop
        execute format('alter table %I.%I rename to %Is;', tables.schema_name, tables.table_name, tables.table_name);
    end loop;
end
$$ language plpgsql;

https://www.postgresql.org/docs/10/static/sql-do.html

  • thank you very much! it worked

  • blz @leoCastro, as solved your problem does not forget to mark as best answer. vlw!

Browser other questions tagged

You are not signed in. Login or sign up in order to post.