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?

sql postgresql plpgsql

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 sql postgresql plpgsql

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