Create a gist now

Instantly share code, notes, and snippets.

Embed
What would you like to do?
See http://justobjects.nl/moving-postgis-tables-from-the-public-schema-to-a-new-schema/ Instead of the default public schema where PostGIS and its meta-tables (geometry_columns and spatial_ref_sys) are installed one can use an explicit schema. One main reason, at least for me, is that PostgreSQL Schema’s allow me to make data dumps of the Schema…
-- Function to move all tables from old to new schema
CREATE OR REPLACE FUNCTION
public.postgis_schema_move(old_schema varchar(32),
new_schema varchar(32)) RETURNS void AS $$
DECLARE
row record;
BEGIN
FOR row IN SELECT tablename FROM pg_tables
WHERE schemaname = old_schema and tablename != 'spatial_ref_sys'
AND tablename != 'geometry_columns'
LOOP
EXECUTE 'ALTER TABLE ' || quote_ident(old_schema) || '.'
|| quote_ident(row.tablename) ||
' SET SCHEMA ' || new_schema || ';';
EXECUTE 'UPDATE public.geometry_columns
SET f_table_schema = ' || quote_literal(new_schema) ||
' WHERE f_table_schema = ' || quote_literal(old_schema) ||'
AND f_table_name = ' || quote_literal(row.tablename) || ';';
END LOOP;
END;
$$ LANGUAGE plpgsql;
-- Function to move single table from old to new schema
CREATE OR REPLACE FUNCTION
public.postgis_table_move(old_schema varchar(32),
new_schema varchar(32), target_table varchar(32)) RETURNS void AS $$
BEGIN
EXECUTE 'ALTER TABLE ' || quote_ident(old_schema) || '.'
|| quote_ident(target_table) ||
' SET SCHEMA ' || new_schema || ';';
EXECUTE 'UPDATE public.geometry_columns
SET f_table_schema = ' || quote_literal(new_schema) ||
' WHERE f_table_schema = ' || quote_literal(old_schema) ||'
AND f_table_name = ' || quote_literal(target_table) || ';';
END;
$$ LANGUAGE plpgsql;
-- EXAMPLES
-- Example: Move from public to schema app
-- SELECT public.postgis_schema_move('public', 'app');
-- Example: Move single table from public to schema app
-- SELECT public.postgis_table_move('public', 'app', 'mytable');
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment