Add Replacers.replacingTable(Table<?>, Table<?>) and related utilities

[lukaseder]

A very useful application of the Replacer API is to replace a Table<?> by another. For example, when implementing CockroachDB UDF support (#13947), the INFORMATION_SCHEMA.ROUTINES and INFORMATION_SCHEMA.PARAMETERS views cannot be used yet (see cockroachdb/cockroach#104083), so it would be useful to be able to substitute all references to these views by their definition in the form of a correlated subquery. E.g. instead of:

select
  r1.routine_schema,
  r1.routine_name,
  r1.specific_name,
  r1.routine_type,
  case
    when (
      r1.data_type = 'USER-DEFINED'
      and r1.type_udt_name = 'geometry'
    ) then 'geometry'
    when (pg_catalog.pg_proc.proargmodes && ARRAY['o','b']::"char"[]) then 'void'
    when r1.data_type = 'ARRAY' then (substring(r1.type_udt_name, 2) || ' ARRAY')
    else r1.data_type
  end as data_type,
  r1.character_maximum_length,
  case
    when (
      r1.numeric_precision is null
      and r1.data_type in (
        'time', 'timetz', 'time without time zone', 'time with time zone', 'timestamp',
        'timestamptz', 'timestamp without time zone', 'timestamp with time zone'
      )
    ) then 6
    else r1.numeric_precision
  end as numeric_precision,
  r1.numeric_scale,
  r1.type_udt_schema,
  case
    when r1.data_type = 'ARRAY' then substring(r1.type_udt_name, 2)
    else r1.type_udt_name
  end as type_udt_name,
  case
    when count(*) over (partition by r1.routine_schema, r1.routine_name) > 1 then row_number() over (
      partition by r1.routine_schema, r1.routine_name
      order by r1.specific_name
    )
  end as overload,
  (pg_catalog.pg_proc.prokind = 'a') as is_agg
from information_schema.routines as r1
  join (
    pg_catalog.pg_proc
      join pg_catalog.pg_namespace as alias_120026365
        on pg_catalog.pg_proc.pronamespace = alias_120026365.oid
    )
    on (
      alias_120026365.nspname = r1.specific_schema
      and ((pg_catalog.pg_proc.proname || '_') || cast(pg_catalog.pg_proc.oid as string)) = r1.specific_name
    )
  left outer join pg_catalog.pg_type as rett
    on pg_catalog.pg_proc.prorettype = rett.oid
where (
  r1.routine_schema in ('public')
  and not (pg_catalog.pg_proc.proretset)
  and r1.data_type is distinct from 'trigger'
)
order by r1.routine_schema asc, r1.routine_name asc, overload asc

We'll run:

select
  r1.routine_schema,
  r1.routine_name,
  r1.specific_name,
  r1.routine_type,
  case
    when (
      r1.data_type = 'USER-DEFINED'
      and r1.type_udt_name = 'geometry'
    ) then 'geometry'
    when (pg_catalog.pg_proc.proargmodes && ARRAY['o','b']::"char"[]) then 'void'
    when r1.data_type = 'ARRAY' then (substring(r1.type_udt_name, 2) || ' ARRAY')
    else r1.data_type
  end as data_type,
  r1.character_maximum_length,
  case
    when (
      r1.numeric_precision is null
      and r1.data_type in (
        'time', 'timetz', 'time without time zone', 'time with time zone', 'timestamp',
        'timestamptz', 'timestamp without time zone', 'timestamp with time zone'
      )
    ) then 6
    else r1.numeric_precision
  end as numeric_precision,
  r1.numeric_scale,
  r1.type_udt_schema,
  case
    when r1.data_type = 'ARRAY' then substring(r1.type_udt_name, 2)
    else r1.type_udt_name
  end as type_udt_name,
  case
    when count(*) over (partition by r1.routine_schema, r1.routine_name) > 1 then row_number() over (
      partition by r1.routine_schema, r1.routine_name
      order by r1.specific_name
    )
  end as overload,
  (pg_catalog.pg_proc.prokind = 'a') as is_agg
from (
  select
    alias_29657451.nspname as specific_schema,
    ((p.proname || '_') || cast(p.oid as string)) as specific_name,
    alias_29657451.nspname as routine_schema,
    p.proname as routine_name,
    'f' as routine_type,
    case
      when p.prokind = 'p' then null
      when (
        alias_95693412.typelem <> 0
        and alias_95693412.typlen = -1
      ) then 'ARRAY'
      when alias_100611083.nspname = 'pg_catalog' then format_type(alias_95693412.oid, null)
      else 'USER-DEFINED'
    end as data_type,
    alias_95693412.typname as type_udt_name,
    alias_100611083.nspname as type_udt_schema,
    null as character_maximum_length,
    null as numeric_precision,
    null as numeric_scale,
    null as type_udt_schema
  from (
    pg_catalog.pg_proc as p
      join pg_catalog.pg_namespace as alias_29657451
        on p.pronamespace = alias_29657451.oid
      join (
        pg_catalog.pg_type as alias_95693412
          join pg_catalog.pg_namespace as alias_100611083
            on alias_95693412.typnamespace = alias_100611083.oid
      )
        on p.prorettype = alias_95693412.oid
    )
) as r1
  join (
    pg_catalog.pg_proc
      join pg_catalog.pg_namespace as alias_120026365
        on pg_catalog.pg_proc.pronamespace = alias_120026365.oid
    )
    on (
      alias_120026365.nspname = r1.specific_schema
      and ((pg_catalog.pg_proc.proname || '_') || cast(pg_catalog.pg_proc.oid as string)) = r1.specific_name
    )
  left outer join pg_catalog.pg_type as rett
    on pg_catalog.pg_proc.prorettype = rett.oid
where (
  r1.routine_schema in ('public')
  and not (pg_catalog.pg_proc.proretset)
  and r1.data_type is distinct from 'trigger'
)
order by r1.routine_schema asc, r1.routine_name asc, overload asc

Users can obviously write the Replacer themselves, but they'd have to think of all the edge cases, including:

• Direct references to TableImpl
• Aliased TableImpl
• Direct references to TableFieldImpl
• Aliased TableImpl references to TableFieldImpl
• Possibly others

Tasks:

• Add a simple Replacers.replacingTable() utility
• Add an SPI that allows for composing these more simply, in case multiple such replacements are necessary
• Possibly add a feature set that pulls up all these replacements to CTE