获取PG 表

with base as (

SELECT
  ns.nspname,
  pc.relname,
  pc.relkind
FROM pg_class pc
JOIN pg_namespace ns on ns.oid = pc.relnamespace
WHERE pc.relkind in ('r', 'p', 'f', 'm', 'v')
AND pc.reloptions @> array['refresh_mode=''incremental''']
AND ns.nspname NOT IN ('system', 'information_schema', 'pg_catalog', '_tacnode')
)
SELECT
  nspname,
  relname,
  relkind
FROM base

获取表的列属性

with columns as (
  SELECT
    ns.nspname,
    pc.relname,
    attr.attrelid,
    attr.attnum,
    attr.attname,
    format_type(attr.atttypid, attr.atttypmod) AS data_type,
    attr.attnotnull,
    attr.attgenerated::text AS attgenerated,
    pg_get_expr(def.adbin, def.adrelid) AS expression,
    pg_get_serial_sequence(quote_ident(ns.nspname) || '.' || quote_ident(pc.relname), attr.attname) AS sequence_name
  FROM pg_catalog.pg_attribute attr
  JOIN pg_catalog.pg_attrdef def on def.adrelid = attr.attrelid AND def.adnum = attr.attnum
  JOIN pg_class pc on pc.oid = attr.attrelid
  JOIN pg_namespace ns on pc.relnamespace = ns.oid
  WHERE attr.attnum > 0
    AND NOT attr.attisdropped
)
SELECT * FROM columns
WHERE attrelid = 't'::regclass::oid
ORDER BY attnum

获取表的index

with index_columns AS (
    SELECT
      idx.indexrelid AS index_oid,
      ns.nspname AS index_schema,
      idx.indrelid AS table_oid,
      pc_table.relname AS table_name,
      pc_idx.relname AS index_name,
      am.amname AS index_method,
      attr.attname,
      idx.indisunique,
      idx.indisprimary,
      array_position(idx.indkey, attr.attnum) AS index_column_sequence,
      CASE WHEN indoption[array_position(idx.indkey, attr.attnum)] & 1 != 0  THEN 'DESC' ELSE 'ASC' END AS index_column_order,
      CASE WHEN indoption[array_position(idx.indkey, attr.attnum)] & 2 != 0  THEN 'NULLS FIRST' ELSE 'NULLS LAST' END AS index_column_nulls_order,
      array_position(idx.indkey, attr.attnum) > indnkeyatts AS included_column,
      pg_get_expr(idx.indpred, idx.indrelid) AS partial_expression
    FROM pg_catalog.pg_index idx
    JOIN (SELECT * FROM pg_catalog.pg_class WHERE relkind = 'i') pc_idx on pc_idx.oid = idx.indexrelid
    JOIN (SELECT * FROM pg_catalog.pg_class WHERE relkind in ('r', 'm', 'f')) pc_table ON pc_table.oid = idx.indrelid
    JOIN pg_catalog.pg_namespace ns on ns.oid = pc_table.relnamespace
    JOIN pg_am am on am.oid = pc_idx.relam
    JOIN (SELECT * FROM pg_catalog.pg_attribute WHERE attnum > 0 and not attisdropped) attr on attr.attrelid = idx.indrelid and attr.attnum = any(idx.indkey)
), indexes as (
  SELECT
    index_oid,
    any_value(index_schema) AS index_schema,
    any_value(table_oid) AS table_oid,
    any_value(table_name) AS table_name,
    any_value(index_name) AS index_name,
    any_value(index_method) AS index_method,
    any_value(indisunique) AS indisunique,
    any_value(indisprimary) AS indisprimary,
    CASE 
      WHEN any_value(index_method) = 'btree' THEN string_agg(quote_ident(attname) || ' ' || index_column_order || ' ' || index_column_nulls_order, ',' ORDER BY index_column_sequence) FILTER(WHERE NOT included_column) 
      ELSE string_agg(quote_ident(attname), ',' ORDER BY index_column_sequence)
    END AS index_columns,
    CASE WHEN any_value(index_method) = 'btree' THEN string_agg(quote_ident(attname), ',' ORDER BY index_column_sequence) FILTER(WHERE included_column) ELSE '' END AS included_columns,
    any_value(partial_expression) AS partial_expression
  FROM index_columns
  GROUP BY index_oid
), index_meta AS (
  SELECT
    index_schema,
    table_name,
    index_oid,
    index_method,
    indisunique,
    indisprimary,
    CASE
      WHEN indisprimary THEN 'ALTER TABLE ' || quote_ident(index_schema) || '.' || quote_ident(table_name) || ' ADD PRIMARY KEY(' || index_columns || ')'
      ELSE  'CREATE ' || CASE WHEN indisunique THEN 'UNIQUE ' ELSE '' END || 'INDEX ' || quote_ident(index_name) || ' ON ' || quote_ident(index_schema) || '.' || quote_ident(table_name) || 
        ' USING ' || index_method || '(' || index_columns || ')' || 
        CASE WHEN included_columns IS NOT NULL AND included_columns != '' THEN ' INCLUDE ' || included_columns ELSE '' END ||
        CASE WHEN partial_expression IS NOT NULL AND partial_expression != '' THEN ' WHERE ' || partial_expression ELSE '' END 
    END || ';' AS expr
  FROM indexes
), table_indexes AS (
  SELECT
  FROM index_meta
  GROUP BY 
)

获取表metadata