SHARE
TWEET

Untitled

a guest Sep 11th, 2019 103 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. CREATE OR REPLACE FUNCTION public.clone_schema(
  2.     source_schema text,
  3.     dest_schema text)
  4. RETURNS void AS
  5. $BODY$
  6. DECLARE
  7. object text;
  8. buffer text;
  9. default_ text;
  10. column_ text;
  11. constraint_name_ text;
  12. constraint_def_ text;
  13. trigger_name_ text;
  14. trigger_timing_ text;
  15. trigger_events_ text;
  16. trigger_orientation_ text;
  17. trigger_action_ text;
  18. BEGIN
  19.     -- replace existing schema
  20.     EXECUTE 'DROP SCHEMA IF EXISTS ' || dest_schema || ' CASCADE';
  21.     -- create schema
  22.     EXECUTE 'CREATE SCHEMA ' || dest_schema ;
  23.     -- create sequences
  24.     FOR object IN
  25.         SELECT sequence_name::text FROM information_schema.SEQUENCES WHERE sequence_schema = source_schema
  26.         LOOP
  27.             EXECUTE 'CREATE SEQUENCE ' || dest_schema || '.' || object;
  28. END LOOP;
  29.  
  30. -- create tables
  31. FOR object IN
  32.     SELECT table_name::text FROM information_schema.TABLES WHERE table_schema = source_schema
  33.     LOOP
  34.         buffer := dest_schema || '.' || object;
  35.         -- create table
  36.         EXECUTE 'CREATE TABLE ' || buffer || ' (LIKE ' || source_schema || '.' || object || ' INCLUDING CONSTRAINTS INCLUDING INDEXES INCLUDING DEFAULTS)';
  37.         -- fix sequence defaults
  38.         FOR column_, default_ IN
  39.             SELECT column_name::text, REPLACE(column_default::text, source_schema||'.', dest_schema||'.') FROM information_schema.COLUMNS WHERE table_schema = dest_schema AND table_name = object AND column_default LIKE 'nextval(%' || source_schema || '.%::regclass)'
  40.             LOOP
  41.                 EXECUTE 'ALTER TABLE ' || buffer || ' ALTER COLUMN ' || column_ || ' SET DEFAULT ' || default_;
  42. END LOOP;
  43. -- create triggers
  44. FOR trigger_name_, trigger_timing_, trigger_events_, trigger_orientation_, trigger_action_ IN
  45.     SELECT trigger_name::text, action_timing::text, string_agg(event_manipulation::text, ' OR '), action_orientation::text, action_statement::text FROM information_schema.TRIGGERS WHERE event_object_schema=source_schema and event_object_table=object GROUP BY trigger_name, action_timing, action_orientation, action_statement
  46.     LOOP
  47.         EXECUTE 'CREATE TRIGGER ' || trigger_name_ || ' ' || trigger_timing_ || ' ' || trigger_events_ || ' ON ' || buffer || ' FOR EACH ' || trigger_orientation_ || ' ' || trigger_action_;
  48. END LOOP;
  49. END LOOP;
  50. -- reiterate tables and create foreign keys
  51. FOR object IN
  52.     SELECT table_name::text FROM information_schema.TABLES WHERE table_schema = source_schema
  53.     LOOP
  54.         buffer := dest_schema || '.' || object;
  55.         -- create foreign keys
  56.         FOR constraint_name_, constraint_def_ IN
  57.             SELECT conname::text, REPLACE(pg_get_constraintdef(pg_constraint.oid), source_schema||'.', dest_schema||'.') FROM pg_constraint INNER JOIN pg_class ON conrelid=pg_class.oid INNER JOIN pg_namespace ON pg_namespace.oid=pg_class.relnamespace WHERE contype='f' and relname=object and nspname=source_schema
  58.             LOOP
  59.                 EXECUTE 'ALTER TABLE '|| buffer ||' ADD CONSTRAINT '|| constraint_name_ ||' '|| constraint_def_;
  60. END LOOP;
  61. END LOOP;
  62.  
  63. END;
  64.  
  65. $BODY$
  66. LANGUAGE plpgsql VOLATILE
  67. COST 100;
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top