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 you an idea?
You can create an anonymous code block through the DO clause, briefly it is a function that can not have a return and you do not have to write an object to the database.
In function a loop will be performed in the SQL query result and for each tuple the command will be executed to rename the table.
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;