I have a PostgreSQL 9.1 database with 100 or so tables that were loaded into the 'public' schema. I would like to move those tables (but not all of the functions in 'public') to a 'data' schema.
I know that I can use the following to move 1 table at a time.
ALTER TABLE [tablename] SET SCHEMA [new_schema]
Is it possible to move all of the tables to the new schema in one operation? If so, what would be the most efficient way to accomplish this task?
DO will do the trick:
DO
$$
DECLARE
row record;
BEGIN
FOR row IN SELECT tablename FROM pg_tables WHERE schemaname = 'public' -- and other conditions, if needed
LOOP
EXECUTE format('ALTER TABLE public.%I SET SCHEMA [new_schema];', row.tablename);
END LOOP;
END;
$$;
DO$$ DECLARE row record; sql text = E'\n'; BEGIN FOR row IN select proname::text as proname, pg_get_function_identity_arguments(p.oid) AS params from pg_proc p JOIN pg_type t on p.prorettype = t.oid JOIN pg_namespace n on n.oid = p.pronamespace LEFT JOIN pg_description d on p.oid = d.objoid WHERE nspname = 'public' LOOP sql = CONCAT(sql, E'\n', 'ALTER FUNCTION public.', row.proname, '(', row.params, ') SET schema [new_schema];'); END LOOP; EXECUTE sql; END;$$;
-- ####### USING DBEAVER WHICH SUPPORT VARIABLES ########
-- @@@ ANSWER_1 -- USING DO @@@--------
-- Step1: Set variables one by one
@set _SCHEMA = 'public'
@set _COLUMN = 'dml_status'
@set _DATA_TYPE = 'integer'
@set _DEFAULT = '1'
-- Step2: Call the below procedure
DO
$$
DECLARE
row record;
query varchar;
BEGIN
FOR ROW IN SELECT table_name FROM information_schema.tables WHERE table_schema = ${_SCHEMA}
LOOP
query :='ALTER TABLE public.' || quote_ident(row.table_name) ||' ADD COLUMN IF NOT EXISTS '||${_COLUMN} || ' ' || ${_DATA_TYPE} ||' not null default ' || ${_DEFAULT} || ';' ;
execute query;
END LOOP;
END;
$$;
-- @@@ ANSWER_2 -- STORE PROCEDURE FN @@@--------
DROP FUNCTION addColumnToMultipleTables cascade;
create or replace function addColumnToMultipleTables()
returns void
LANGUAGE 'plpgsql'
as $$
DECLARE
row record;
query varchar;
BEGIN
FOR ROW IN SELECT table_name FROM information_schema.tables WHERE table_schema = ${_SCHEMA}
LOOP
query :='ALTER TABLE public.' || quote_ident(row.table_name) ||' ADD COLUMN IF NOT EXISTS '||${_COLUMN} || ' ' || ${_DATA_TYPE} ||' not null default ' || ${_DEFAULT} || ';' ;
raise info 'query : % ', query;
execute query;
END LOOP;
END;
$$;
select addColumnToMultipleTables();
In psql prompt, The below query generates the list of ALTER TABLE statements at once, we can execute the list of statements to move tables from the "public" schema to "new_schema".
Update source schema(here: public) and target schema(here: new_schema) accordingly.
SELECT 'ALTER TABLE ' || quote_ident(schemaname) || '.'
|| quote_ident(tablename) || ' SET SCHEMA new_schema;'
FROM pg_tables
WHERE schemaname = 'public';
Sample Output:
+--------------------------------------------------+
| ?column? |
+--------------------------------------------------+
| ALTER TABLE public.error SET SCHEMA new_schema; |
| ALTER TABLE public.error1 SET SCHEMA new_schema; |
| ALTER TABLE public.error2 SET SCHEMA new_schema; |
| ALTER TABLE public.test SET SCHEMA new_schema; |
+--------------------------------------------------+
