Update docs around custom plugins for V5

[dinodeSimon]

Feature description

The example provided on https://github.com/graphile/pg-aggregates#defining-your-own-grouping-derivatives to add month / day / quarter etc to timestamp fields use the V4 APIs and need to be updated to support V5

[benjie]

Moved to pg-aggregates plugin repo.

[benjie]

In the mean time, please apply the recommendations from this guide to the documentation: https://postgraphile.org/postgraphile/next/migrating-from-v4/migrating-custom-plugins

[dinodeSimon]

The code below was created as a poc with benjie to work with V5 and could possibly be used to update the docs

// Constants from PostgreSQL
const TIMESTAMP_OID = "1114";
const TIMESTAMPTZ_OID = "1184";
const DATE_OID = "1082";

// Determine if a given type is a timestamp/timestamptz
/** @type {(pgType: import('postgraphile/@dataplan/pg').PgCodec) => boolean} */
const isTimestamp = (pgType) =>
  pgType.extensions.oid === TIMESTAMP_OID ||
  pgType.extensions.oid === TIMESTAMPTZ_OID;

// Determine if a given type is a date
/** @type {(pgType: import('postgraphile/@dataplan/pg').PgCodec) => boolean} */
const isDatestamp = (pgType) => {
  pgType.extensions.oid === DATE_OID;
};

// Build a spec that truncates to the given interval
/** @type{(sql: any, interval: any) => import('@graphile/pg-aggregates').AggregateGroupBySpec} s*/
const tsTruncateSpec = (sql, interval) => ({
  // `id` has to be unique, derive it from the `interval`:
  id: `truncated-to-${interval}`,

  // Only apply to timestamp fields:
  isSuitableType: isTimestamp,

  // Given the column value represented by the SQL fragment `sqlFrag`, wrap it
  // with a `date_trunc()` call, passing the relevant interval.
  sqlWrap: (sqlFrag) =>
    sql.fragment`date_trunc(${sql.literal(interval)}, ${sqlFrag})`,
});

const dsTruncateSpec = (sql, interval) => ({
  // `id` has to be unique, derive it from the `interval`:
  id: `date-truncated-to-${interval}`,

  // Only apply to timestamp fields:
  isSuitableType: isDatestamp,

  // Given the column value represented by the SQL fragment `sqlFrag`, wrap it
  // with a `date_trunc()` call, passing the relevant interval.
  sqlWrap: (sqlFrag) =>
    sql.fragment`date_trunc(${sql.literal(interval)}, ${sqlFrag})`,
});
// This is the PostGraphile plugin; see:
// https://www.graphile.org/postgraphile/extending/

/** @type {GraphileConfig.Plugin} */
const DateTruncAggregateGroupSpecsPlugin = {
  name: "DateTruncAggregateGroupSpecsPlugin",
  after: ["PgAggregatesSpecsPlugin"],
  schema: {
    hooks: {
      build(build) {
        const { pgSql: sql } = build;

        build.pgAggregateGroupBySpecs = [
          // Copy all existing specs, except the ones we're replacing
          ...build.pgAggregateGroupBySpecs.filter(
            (spec) =>
              !["truncated-to-day", "truncated-to-hour"].includes(spec.id)
          ),

          // Add our timestamp specs
          tsTruncateSpec(sql, "quarter"),
          tsTruncateSpec(sql, "year"),
          tsTruncateSpec(sql, "month"),
          tsTruncateSpec(sql, "week"),
          tsTruncateSpec(sql, "day"),
          tsTruncateSpec(sql, "hour"),
          tsTruncateSpec(sql, "minute"),
          // Add our datestamp specs
          dsTruncateSpec(sql, "quarter"),
          dsTruncateSpec(sql, "year"),
          dsTruncateSpec(sql, "month"),
          dsTruncateSpec(sql, "week"),
          dsTruncateSpec(sql, "day"),
          // Other values: microseconds, milliseconds, second, minute, quarter,
          // decade, century, millennium.
          // See https://www.postgresql.org/docs/current/functions-datetime.html#FUNCTIONS-DATETIME-TRUNC
        ];

        return build;
      },
    },
  },
};

const DateTruncAggregateGroupSpecsPreset = {
  plugins: [DateTruncAggregateGroupSpecsPlugin],
};

export default DateTruncAggregateGroupSpecsPreset;

[benjie]

(Edited code to use triple backticks ```ts for code formatting)

[benjie]

@jemgillam please can you integrate this into the v5 branch of the pg-aggregates plugin README? https://github.com/graphile/pg-aggregates/tree/v5