## F.16. dump_stat

The `dump_stat` module provides functions that allow you to backup and recover the contents of the `pg_statistic` table. When performing a dump/restore, you can use `dump_stat` to migrate the original statistics to the new server instead of running the `ANALYZE` command for the whole database cluster, which can significantly reduce downtime for large databases. The `dump_statistic` function generates `INSERT` statements which can later be applied to a compatible database. To successfully restore statistical data, you must install the extension on both the original and the recipient servers since these statements rely on the provided `dump_stat` functions.

Note that the definition of the `pg_statistic` table might change occasionally, which means that generated dump might be incompatible with future releases of Postgres Pro Enterprise.

### F.16.1. Installation

The `dump_stat` extension is included into Postgres Pro Enterprise. Once you have Postgres Pro Enterprise installed, you must execute the CREATE EXTENSION command to enable `dump_stat`, as follows:

    CREATE EXTENSION dump_stat;

### F.16.2. Functions

`anyarray_to_text(array anyarray) returns text`

  Returns the given array as `text`.

`dump_statistic() returns setof text`

  `dump_statistic` dumps the contents of the `pg_statistic` system catalog. It produces an `INSERT` statement per each tuple of the `pg_statistic`, excluding the ones that contain statistical data for tables in the `information_schema` and `pg_catalog` schemas.

  The `INSERT` statement takes form of

      WITH upsert as (
        UPDATE pg_catalog.pg_statistic SET column_name = expression [, ...]
        WHERE starelid = t_relname::regclass
          AND to_attname(t_relname, staattnum) = t_attname
          AND to_atttype(t_relname, staattnum) = t_atttype
          AND stainherit = t_stainherit
        RETURNING *)
      ins as (
        SELECT expression [, ...]
        WHERE NOT EXISTS (SELECT * FROM upsert)
          AND to_attnum(t_relname, t_attname) IS NOT NULL
          AND to_atttype(t_relname, t_attname) = t_atttype)
      INSERT INTO pg_catalog.pg_statistic SELECT * FROM ins;

      where expression can be one of:

      array_in(array_text, type_name::regtype::oid, -1)
      value::type_name

  To save the produced statements, redirect the psql output into a file using standard psql options. For details on the available psql options, see psql. Meta-commands starting with a backslash are not supported.

  For example, to save statistics for the `dbname` database into a `dump_stat.sql` file, run:

      $ psql -XAtq -c "SELECT dump_statistic()" dbname > dump_stat.sql

`dump_statistic(schema_name text) returns setof text`

  `dump_statistic` dumps the contents of the `pg_statistic` system catalog. It produces an `INSERT` statement per each tuple of the `pg_statistic` that relates to some table in the `schema_name` schema.

`dump_statistic(schema_name text, table_name text) returns setof text`

  `dump_statistic` dumps the contents of the `pg_statistic` system catalog. It produces an `INSERT` statement per each tuple of the `pg_statistic` that relates to the specified `schema_name.table_name` table.

`dump_statistic(relation regclass) returns setof text`

  `dump_statistic` dumps the contents of the `pg_statistic` system catalog. It produces an `INSERT` statement per each tuple of the `pg_statistic` that contains statistical data for the specified `relation`.

`to_schema_qualified_operator(opid oid) returns text`

  Fetches the schema-qualified operator name by operator id `opid`. For example:

      test=# SELECT to_schema_qualified_operator('+(int,int)'::regoperator);
                to_schema_qualified_operator
      ------------------------------------------------
       pg_catalog.+(pg_catalog.int4, pg_catalog.int4)
      (1 row)

`to_schema_qualified_type(typid oid) returns text`

  Fetches the schema-qualified type name by type id `typid`.

`to_schema_qualified_relation(relid oid) returns text`

  Fetches the schema-qualified relation name by relation id `relid`.

`anyarray_elemtype(arr anyarray) returns oid`

  Returns the element type of the given array as `oid`. For example:

      test=# SELECT anyarray_elemtype(array_in('{1,2,3}', 'int'::regtype, -1));
       anyarray_elemtype
      -------------------
                      23
      (1 row)

`to_attname(relation regclass, colnum int2) returns text`

  Given a relation name `relation` and a column number `colnum`, returns the column name as `text`.

`to_attnum(relation regclass, col text) returns int2`

  Given a relation name `relation` and a column name `col`, returns the column number as `int2`.

`to_atttype(relation regclass, col text) returns text`

  Given a relation name `relation` and a column name `col`, returns the schema-qualified column type as `text`.

`to_atttype(relation regclass, colnum int2) returns text`

  Given a relation name `relation` and a column number `colnum`, returns the schema-qualified column type as `text`.

`to_namespace(nsp text) returns oid`

  `to_namespace` duplicates the behavior of the cast to the `regnamespace` type, which is not present in the PostgreSQL 9.4 release (and prior releases). This function returns the `oid` of the given schema.

`get_namespace(relation oid) returns oid`

  `get_namespace` returns the schema of the given relation as `oid`.