github.com/turbot/steampipe@v1.7.0-rc.0.0.20240517123944-7cef272d4458/pkg/db/db_local/sql_clone.go (about)

     1  package db_local
     2  
     3  const cloneForeignSchemaSQL = `CREATE OR REPLACE FUNCTION clone_foreign_schema(
     4      source_schema text,
     5      dest_schema text,
     6      plugin_name text)
     7      RETURNS text AS
     8  $BODY$
     9  
    10  DECLARE	
    11      src_oid          oid;
    12      object           text;
    13      dest_table       text;
    14      table_sql        text;
    15      columns_sql      text;
    16      type_            text;
    17      column_          text;
    18      underlying_type  text;
    19      res              text;
    20  BEGIN
    21  
    22      -- Check that source_schema exists
    23      SELECT oid INTO src_oid
    24      FROM pg_namespace
    25      WHERE nspname = source_schema;
    26      IF NOT FOUND
    27      THEN
    28          RAISE EXCEPTION 'source schema % does not exist!', source_schema;
    29          RETURN '';
    30      END IF;
    31  
    32      -- Create schema
    33      EXECUTE 'DROP SCHEMA IF EXISTS "' ||  dest_schema || '" CASCADE';
    34      EXECUTE 'CREATE SCHEMA "' || dest_schema || '"';
    35      EXECUTE 'GRANT USAGE ON SCHEMA "' || dest_schema || '" TO steampipe_users';
    36      EXECUTE 'ALTER DEFAULT PRIVILEGES IN SCHEMA "' || dest_schema || '" GRANT SELECT ON TABLES TO steampipe_users';
    37  
    38      -- Create tables
    39      FOR object IN
    40          SELECT TABLE_NAME::text
    41          FROM information_schema.tables
    42          WHERE table_schema = source_schema
    43            AND table_type = 'FOREIGN'
    44      LOOP
    45          columns_sql := '';
    46  
    47          FOR column_, type_ IN
    48              SELECT c.column_name::text, 
    49                     CASE 
    50                         WHEN c.data_type = 'USER-DEFINED' THEN t.typname
    51                         ELSE c.data_type
    52                     END as data_type
    53              FROM information_schema.COLUMNS c
    54              LEFT JOIN pg_catalog.pg_type t ON c.udt_name = t.typname
    55              WHERE c.table_schema = source_schema
    56                AND c.TABLE_NAME = object
    57          LOOP
    58              IF columns_sql <> ''
    59              THEN
    60                  columns_sql = columns_sql || ',';
    61              END IF;
    62              columns_sql = columns_sql || quote_ident(column_) || ' ' || type_;
    63          END LOOP;
    64  
    65          dest_table := '"' || dest_schema || '".' || quote_ident(object);
    66          table_sql :='CREATE FOREIGN TABLE ' || dest_table || ' (' || columns_sql || ') SERVER steampipe OPTIONS (table '|| $$'$$ || quote_ident(object) || $$'$$ || ') ';
    67          EXECUTE table_sql;
    68  
    69          SELECT CONCAT(res, table_sql, ';') into res;
    70      END LOOP;
    71      RETURN res;
    72  END
    73  
    74  $BODY$
    75  LANGUAGE plpgsql VOLATILE
    76                   COST 100;
    77  `
    78  
    79  const cloneCommentsSQL = `
    80  CREATE OR REPLACE FUNCTION clone_table_comments(
    81      source_schema text,
    82      dest_schema text)
    83      RETURNS text AS
    84  $BODY$
    85  
    86  DECLARE
    87      src_oid         oid;
    88      dest_oid        oid;
    89      t               text;
    90      ret             text;
    91      query           text;
    92      table_desc      text;
    93      column_desc     text;
    94      column_number   int;
    95      c               text;
    96  BEGIN
    97  
    98      -- Check that source_schema and dest_schema exist
    99      SELECT oid INTO src_oid
   100      FROM pg_namespace
   101      WHERE nspname = quote_ident(source_schema);
   102      IF NOT FOUND
   103      THEN
   104          RAISE NOTICE 'source schema % does not exist!', source_schema;
   105          RETURN 'source schema does not exist!';
   106      END IF;
   107  
   108      SELECT oid INTO dest_oid
   109      FROM pg_namespace
   110      WHERE nspname = quote_ident(dest_schema);
   111      IF NOT FOUND
   112      THEN
   113          RAISE NOTICE 'dest schema % does not exist!', dest_schema;
   114          RETURN 'dest schema does not exist!';
   115      END IF;
   116  
   117  
   118      -- Copy comments
   119      FOR t IN
   120          SELECT table_name::text
   121          FROM information_schema.tables
   122              WHERE table_schema = quote_ident(source_schema)
   123              AND table_type = 'FOREIGN'
   124      LOOP
   125          SELECT OBJ_DESCRIPTION((quote_ident(source_schema) || '.' || quote_ident(t))::REGCLASS) INTO table_desc;
   126          query = 'COMMENT ON FOREIGN TABLE ' || quote_ident(dest_schema) ||  '.' || quote_ident(t) || ' IS $steampipe_escape$' || table_desc || '$steampipe_escape$';
   127         SELECT CONCAT(ret, query || '\n') INTO ret;
   128          EXECUTE query;
   129  
   130          FOR  c,column_number IN
   131              SELECT column_name, ordinal_position
   132              FROM information_schema.COLUMNS
   133                  WHERE table_schema = quote_ident(source_schema)
   134                  AND table_name = quote_ident(t)
   135          LOOP
   136              SELECT PG_CATALOG.COL_DESCRIPTION((quote_ident(source_schema) || '.' || quote_ident(t))::REGCLASS::OID, column_number) INTO column_desc;
   137              query = 'COMMENT ON COLUMN ' || quote_ident(dest_schema) ||  '.' || quote_ident(t) ||  '.' || quote_ident(c) || ' IS $steampipe_escape$' || column_desc || '$steampipe_escape$';
   138  --            SELECT CONCAT(ret, query || '\n') INTO ret;
   139              EXECUTE query;
   140          END LOOP;
   141      END LOOP;
   142  
   143      RETURN ret;
   144  END
   145  
   146  $BODY$
   147      LANGUAGE plpgsql VOLATILE
   148                       COST 100;
   149  `