Extraction Queries for Greenplum

Alation Cloud Service Applies to Alation Cloud Service instances of Alation

Customer Managed Applies to customer-managed instances of Alation

The default extraction queries for the Greenplum data source are provided below. You can customize them to better suit your catalog needs. Custom queries can be specified on the Metadata Extraction tab of the data source Settings page under Metadata Extraction Queries.

You can customize all or some of the queries.

Schema

Ensure your query has a column labeled as CATALOG in the SELECT list.

SELECT
  CURRENT_DATABASE() AS CATALOG,
  nspname AS SCHEMA
FROM
  PG_NAMESPACE
WHERE
  nspname NOT LIKE 'pg\_%'
  AND nspname NOT IN ('''')
  AND nspname NOT IN (
    'information_schema',
    'pg_toast',
    'pg_catalog',
    'pg_internal');

Table

Ensure your query has columns labeled as CATALOG, TABLE_NAME, TABLE_TYPE, REMARKS in the SELECT list.

SELECT
  CURRENT_DATABASE() AS CATALOG,
  A.TABLE_SCHEM AS SCHEMA,
  A.TABLE_NAME,
  A.OWNER AS TABLE_OWNER,
  A.TABLE_TYPE,
  PGDC.DESCRIPTION AS REMARKS
FROM
  (
  SELECT
    T.SCHEMANAME AS TABLE_SCHEM,
    T.TABLENAME AS TABLE_NAME,
    T.TABLEOWNER AS OWNER,
    'TABLE' AS TABLE_TYPE
  FROM
    PG_TABLES T
  WHERE
    (
      T.SCHEMANAME || '.' || T.TABLENAME
    ) NOT IN (
    SELECT
      ST.SCHEMANAME || '.' || ST.RELNAME
    FROM
      PG_INHERITS,
      PG_CATALOG.PG_STAT_ALL_TABLES ST
    WHERE
      PG_INHERITS.INHRELID = ST.RELID)) A
  LEFT JOIN
  (
    SELECT
      C.SCHEMANAME,
      C.RELNAME,
      PGD.DESCRIPTION
    FROM PG_CATALOG.PG_STAT_ALL_TABLES C
      INNER JOIN PG_CATALOG.PG_DESCRIPTION PGD
        ON  (PGD.OBJOID = C.RELID)
    WHERE
      PGD.OBJSUBID IS NULL OR PGD.OBJSUBID = 0) PGDC
  ON (PGDC.RELNAME = A.TABLE_NAME AND PGDC.SCHEMANAME = A.TABLE_SCHEM)
WHERE
  A.TABLE_SCHEM NOT LIKE 'pg\_%'
  AND A.TABLE_SCHEM NOT IN ('''')
  AND A.TABLE_SCHEM NOT IN (
      'information_schema', 'pg_toast',
      'pg_catalog', 'pg_internal')
ORDER BY
  TABLE_SCHEM,
  TABLE_NAME;

View

Ensure your query has columns labeled as CATALOG, VIEW_NAME, VIEW_CREATE_STATEMENT, 'VIEW' AS VIEW_TYPE, REMARKS in the SELECT list.

SELECT
  CURRENT_DATABASE() AS CATALOG,
  schemaname AS SCHEMA,
  viewname AS VIEW_NAME,
  ' CREATE OR REPLACE VIEW ' || schemaname || '.' || viewname || ' AS ' || definition AS VIEW_CREATE_STATEMENT,
  'VIEW' AS VIEW_TYPE,
  '' AS REMARKS
FROM
  PG_VIEWS
WHERE
  schemaname NOT LIKE 'pg\%'
  AND schemaname NOT IN ('''')
  AND schemaname NOT IN (
    'information_schema', 'pg_toast',
    'pg_catalog', 'pg_internal'
  )
ORDER BY
  SCHEMA,
  VIEW_NAME;

Column

Ensure your query has columns labeled as CATALOG, TABLE_NAME, TYPE_NAME, DATA_TYPE, COLUMN_NAME, ORDINAL_POSITION, IS_NULLABLE, REMARKS, COLUMN_DEFAULT in the SELECT list.

SELECT *
FROM
  (SELECT
    CURRENT_DATABASE() AS CATALOG,
    S.NSPNAME AS SCHEMA,
    O.RELNAME AS TABLE_NAME,
    C.ATTNAME AS COLUMN_NAME,
    CASE
      WHEN (
            IC.DATA_TYPE = 'numeric'
            OR IC.DATA_TYPE = 'integer'
            OR IC.DATA_TYPE = 'bigint'
            OR IC.DATA_TYPE = 'smallint'
            OR IC.DATA_TYPE = 'real'
            OR IC.DATA_TYPE = 'double precision')
            AND IC.NUMERIC_SCALE IS NOT NULL
            AND IC.NUMERIC_PRECISION IS NOT NULL THEN CONCAT(
                          TYP.TYPNAME,
                          '(',
                          CAST(IC.NUMERIC_PRECISION AS character varying),
                          ',',
                          CAST(IC.NUMERIC_SCALE AS character varying),
                          ')')
      WHEN (
            IC.DATA_TYPE = 'numeric'
            OR IC.DATA_TYPE = 'integer'
            OR IC.DATA_TYPE = 'bigint'
            OR IC.DATA_TYPE = 'smallint'
            OR IC.DATA_TYPE = 'real'
            OR IC.DATA_TYPE = 'double precision')
            AND IC.NUMERIC_SCALE IS NULL
            AND IC.NUMERIC_PRECISION IS NULL THEN TYP.TYPNAME
      WHEN (
            IC.DATA_TYPE = 'character varying'
            OR IC.DATA_TYPE = 'character')
            AND IC.CHARACTER_MAXIMUM_LENGTH IS NULL THEN TYP.TYPNAME
      WHEN (
            IC.DATA_TYPE = 'character varying'
            OR IC.DATA_TYPE = 'character')
            AND IC.CHARACTER_MAXIMUM_LENGTH IS NOT NULL THEN CONCAT(
                          TYP.TYPNAME,
                          '(',
                          CAST(IC.CHARACTER_MAXIMUM_LENGTH AS character varying),
                          ')')
      WHEN  IC.DATA_TYPE = 'interval'
            AND IC.DATETIME_PRECISION IS NOT NULL THEN CONCAT(
                          TYP.TYPNAME,
                          '(',
                          CAST(IC.DATETIME_PRECISION AS character varying),
                          ',',
                          CAST(IC.INTERVAL_TYPE AS character varying),
                          ')')
      WHEN (
            IC.DATA_TYPE = 'time without time zone'
            OR IC.DATA_TYPE = 'timestamptz'
            OR IC.DATA_TYPE = 'date'
            OR IC.DATA_TYPE = 'timestamp with time zone'
            OR IC.DATA_TYPE = 'time with time zone')
            AND IC.DATETIME_PRECISION IS NOT NULL THEN CONCAT(
                          TYP.TYPNAME,
                          '(',
                          CAST(IC.DATETIME_PRECISION AS character varying),
                          ')')
      WHEN  IC.DATA_TYPE = 'time without time zone'
            AND IC.DATETIME_PRECISION IS NULL THEN TYP.TYPNAME
      ELSE TYP.TYPNAME
    END AS TYPE_NAME,
    TYP.TYPNAME AS DATA_TYPE,
    C.ATTNUM AS ORDINAL_POSITION,
    C.ATTNOTNULL AS IS_NULLABLE,
    pgd.description AS REMARKS,
    null AS COLUMN_DEFAULT
  FROM
    PG_ATTRIBUTE C
      INNER JOIN PG_CLASS O
        ON C.ATTRELID = O.OID
      INNER JOIN PG_NAMESPACE S
        ON O.RELNAMESPACE = S.OID
      INNER JOIN PG_TYPE TYP
        ON TYP.OID = C.ATTTYPID
      LEFT JOIN PG_CATALOG.PG_STAT_ALL_TABLES ca
        ON (
            ca.relname = O.RELNAME
            AND ca.schemaname = S.NSPNAME)
      LEFT JOIN PG_CATALOG.PG_DESCRIPTION pgd
        ON (
            pgd.objoid = ca.relid
            AND pgd.objsubid = C.ATTNUM)
      LEFT JOIN INFORMATION_SCHEMA.columns IC
        ON (
            IC.table_schema = S.NSPNAME
            AND IC.table_name = O.RELNAME
            AND IC.column_name = C.ATTNAME)
  WHERE
    C.ATTNUM > 0
    AND nspname NOT LIKE 'pg\\_%'
    AND nspname NOT IN ('''')
    AND nspname NOT IN (
                    'information_schema',
                    'pg_toast',
                    'pg_catalog',
                    'pg_internal')) T
WHERE
  (T.SCHEMA || '.' || T.TABLE_NAME) NOT IN (
      SELECT
        ST.SCHEMANAME || '.' || ST.RELNAME
      FROM
        PG_INHERITS,
        PG_CATALOG.PG_STAT_ALL_TABLES ST
      WHERE
        PG_INHERITS.INHRELID = ST.RELID)
ORDER BY
  SCHEMA,
  TABLE_NAME,
  ORDINAL_POSITION;

Primary Key

Ensure your query has columns labeled as CATALOG, TABLE_NAME, COLUMN_NAME in the SELECT list.

SELECT
  CURRENT_DATABASE() AS CATALOG,
  S.NSPNAME AS SCHEMA,
  C.RELNAME AS TABLE_NAME,
  CONSTR.CONNAME AS PK_NAME,
  ATTR.ATTNAME AS COLUMN_NAME,
  ATTR.ATTNUM AS KEY_SEQ
FROM
  PG_CONSTRAINT CONSTR
  INNER JOIN PG_ATTRIBUTE ATTR
      ON ATTR.ATTNUM = ANY (CONSTR.CONKEY)
        AND ATTR.ATTRELID = CONSTR.CONRELID
  INNER JOIN PG_CLASS C
      ON CONSTR.CONRELID = C.OID
  INNER JOIN PG_NAMESPACE S
      ON C.RELNAMESPACE = S.OID
WHERE
  nspname NOT LIKE 'pg\\_%'
  AND CONSTR.CONTYPE = 'p'
  AND nspname NOT IN ('''')
  AND nspname NOT IN (
      'information_schema',
      'pg_toast',
      'pg_catalog',
      'pg_internal')
ORDER BY
  SCHEMA,
  TABLE_NAME,
  PK_NAME,
  KEY_SEQ;

Foreign Key

Ensure your query has columns labeled as PK_CATALOG, PK_SCHEMA, PK_TABLE, PK_COLUMN, FK_CATALOG, FK_SCHEMA, FK_TABLE, FK_COLUMN in the SELECT list.

SELECT
  CURRENT_DATABASE() AS CATALOG,
  F.FKTABLE_SCHEM AS FK_SCHEMA,
  F.FKTABLE_NAME AS FK_TABLE,
  F.FK_NAME,
  F.FKCOLUMN_NAME AS FK_COLUMN,
  null AS FK_CATALOG,
  F.KEY_SEQ,
  null AS PK_CATALOG,
  P.PKTABLE_SCHEM AS PK_SCHEMA,
  P.PKTABLE_NAME AS PK_TABLE,
  P.PK_NAME,
  P.PKCOLUMN_NAME AS PK_COLUMN
FROM
(
  SELECT  S.NSPNAME AS FKTABLE_SCHEM,
          C.RELNAME AS FKTABLE_NAME,
          CONSTR.CONNAME AS FK_NAME,
          ATTR.ATTNAME AS FKCOLUMN_NAME,
          ATTR.ATTNUM AS KEY_SEQ,
          CONSTR.CONFKEY,
          CONSTR.CONFRELID
  FROM  PG_CONSTRAINT CONSTR
        INNER JOIN PG_ATTRIBUTE ATTR
          ON ATTR.ATTNUM = ANY (CONSTR.CONKEY)
            AND ATTR.ATTRELID = CONSTR.CONRELID
        INNER JOIN PG_CLASS C
          ON CONSTR.CONRELID = C.OID
        INNER JOIN PG_NAMESPACE S
          ON C.RELNAMESPACE = S.OID
            AND CONSTR.CONTYPE = 'f'
  WHERE    nspname NOT LIKE 'pg\\%'
          AND nspname NOT IN ('''')
          AND nspname NOT IN (
            'information_schema',
            'pg_toast',
            'pg_catalog',
            'pg_internal')) F
  INNER JOIN
  (
    SELECT  S.NSPNAME AS PKTABLE_SCHEM,
            C.RELNAME AS PKTABLE_NAME,
            CONSTR.CONNAME AS PK_NAME,
            ATTR.ATTNAME AS PKCOLUMN_NAME,
            ATTR.ATTNUM AS PKEY_SEQ,
            C.OID
    FROM    PG_CONSTRAINT CONSTR
            INNER JOIN PG_ATTRIBUTE ATTR
              ON ATTR.ATTNUM = ANY (CONSTR.CONKEY)
                AND ATTR.ATTRELID = CONSTR.CONRELID
            INNER JOIN PG_CLASS C
              ON CONSTR.CONRELID = C.OID
            INNER JOIN PG_NAMESPACE S
              ON C.RELNAMESPACE = S.OID
                AND (CONSTR.CONTYPE = 'p'
                  OR CONSTR.CONTYPE = 'u')
    WHERE   nspname NOT LIKE 'pg\\%'
            AND nspname NOT IN ('''')
            AND nspname NOT IN (
              'information_schema',
              'pg_toast',
              'pg_catalog',
              'pg_internal')) P
  ON P.PKEY_SEQ = ANY (F.CONFKEY)
    AND P.OID = F.CONFRELID
ORDER BY
  FKTABLE_SCHEM,
  FKTABLE_NAME,
  FK_NAME,
  KEY_SEQ;

Index

Ensure your query has columns labeled as CATALOG, TABLE_NAME, COLUMN_NAME in the SELECT list.

SELECT
  current_database() AS CATALOG,
  stable.schemaname AS SCHEMA,
  ind_parent.relname AS TABLE_NAME,
  ind_col.attname AS COLUMN_NAME,
  ind_child.relname AS INDEX_NAME,
  ind_col.attnum AS POSITION,
  acc_meth.amname AS INDEX_TYPE
FROM
  PG_INDEX index
  JOIN PG_CLASS ind_parent
    ON index.indrelid = ind_parent.oid
  JOIN PG_CLASS ind_child
    ON index.indexrelid = ind_child.oid
  JOIN PG_ATTRIBUTE ind_col
    ON ind_parent.oid = ind_col.attrelid
  JOIN PG_AM acc_meth
    ON ind_child.relam = acc_meth.oid
  JOIN PG_TABLES stable
    ON ind_parent.relname = stable.tablename
WHERE
  ind_parent.relkind = 'r'
  AND ind_col.attnum = ANY(index.indkey)
  AND schemaname NOT LIKE 'pg\_%'
  AND schemaname NOT IN ('''')
  AND schemaname NOT IN (
        'information_schema',
        'pg_toast',
        'pg_catalog',
        'pg_internal')
ORDER BY
  SCHEMA,
  TABLE_NAME,
  COLUMN_NAME;

Function Definition

Ensure your query has columns labeled as FUNCTION_CAT, FUNCTION_NAME, ARG_NAME, TYPE_NAME, ARG_TYPE, ARG_DEF in the SELECT list.

SELECT
  CURRENT_DATABASE() AS FUNCTION_CAT,
  N.NSPNAME AS SCHEMA,
  P.PRONAME AS FUNCTION_NAME,
  P.PRONAME AS ARG_NAME,
  P.PROARGNAMES AS FUNCTION_ARGS,
  P.PRONARGS AS NUM_OF_FUNCTION_ARGS,
  STRING_TO_ARRAY (REPLACE (OIDVECTORTYPES(P.PROARGTYPES),'"',''), ',') AS ARG_TYPE,
  FORMAT_TYPE(P.PRORETTYPE, NULL) AS TYPE_NAME,
  P.PROSRC AS ARG_DEF,
  NULL AS REMARKS
FROM
  PG_PROC P
  INNER JOIN PG_NAMESPACE N
    ON P.PRONAMESPACE = N.OID
  INNER JOIN PG_LANGUAGE L
    ON P.PROLANG = L.OID
WHERE
  (
    L.LANNAME IN ('internal', 'sql')
    AND p.prorettype <> 'pg_catalog.trigger' :: pg_catalog.regtype
      OR L.LANNAME IN ('plpgsql'))
    AND N.NSPNAME NOT LIKE 'pg\\_%'
    AND N.NSPNAME NOT IN ('''')
    AND N.NSPNAME NOT IN (
      'information_schema',
      'pg_toast',
      'pg_catalog',
      'pg_internal')
ORDER BY
  FUNCTION_CAT,
  SCHEMA,
  FUNCTION_NAME;

Function

Ensure your query has columns labeled as FUNCTION_CAT, FUNCTION_NAME in the SELECT list.

SELECT
  CURRENT_DATABASE() AS FUNCTION_CAT,
  S.NSPNAME AS SCHEMA,
  P.PRONAME AS FUNCTION_NAME,
  NULL AS SPECIFIC_NAME,
  NULL AS REMARKS
FROM
  PG_PROC P
  INNER JOIN PG_NAMESPACE S
    ON P.PRONAMESPACE = S.OID
  INNER JOIN PG_LANGUAGE L
    ON P.PROLANG = L.OID
WHERE
  (
  L.LANNAME IN ('internal', 'sql')
  AND p.prorettype <> 'pg_catalog.trigger' :: pg_catalog.regtype
    OR L.LANNAME IN ('plpgsql'))
  AND S.NSPNAME NOT LIKE 'pg\\_%'
  AND S.NSPNAME NOT IN ('''')
  AND S.NSPNAME NOT IN (
    'information_schema',
    'pg_toast',
    'pg_catalog',
    'pg_internal')
ORDER BY
  FUNCTION_CAT,
  SCHEMA,
  FUNCTION_NAME;